error in trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

error in trigger

Author

Topic

ameya_amu
Starting Member

25 Posts

Posted - 2008-08-06 : 08:50:46

i have three table INV_Purchase_Received_Details ,INV_Purchase_Detail and INV_Stock_Detail

i have created following trigger on INV_Purchase_Received_Details

create TRIGGER insertinpurchase_received_details
ON INV_Purchase_Received_Details
for insert
AS

DECLARE
@PDID integer output,
@Model_ID integer output

select @PDID = inserted.PDID, @Model_ID = inserted.Model_ID from inserted

update pd set pd.Quantity_Recieved = pd.Quantity_Recieved + i.Quantity_Received
from Inserted i INNER Join INV_Purchase_Detail pd on pd.Model_ID = i.Model_ID WHERE (pd.PDid = @PDID) AND (pd.Model_ID = @Model_ID)

if not exists(select * from INV_Stock_Detail where model_id =@model_id ) exec('insert into INV_Stock_Detail (model_id,Quantity_In_Stock) values (@model_id,0)')

update stk set stk.Quantity_In_Stock = stk.Quantity_In_Stock + i.Quantity_Received
from Inserted i INNER Join INV_Stock_Detail stk on stk.Model_ID = i.Model_ID WHERE (stk.Model_ID = @Model_ID)

but when i insert the row in INV_Purchase_Received_Details in table it gives me following error
" must declare the variable @model_id "

i am using following query to insert

insert into INV_Purchase_Received_Details(PrID,PDid,PDDid,Model_ID,Quantity_Received,Rate_Per_Unit,Total_amt,Created_By,Created_Date,status) values(@PrID,@PDid,@PDDid,@Model_ID,@Quantity_Received,@Rate_Per_Unit,@total_amt,@Created_By,@Created_Date,@status)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-06 : 09:08:59

You've got a dynamic string that references @model_Id, @model_Id hasn't been declared inside the scope of the EXEC ::::


if not exists(select * from INV_Stock_Detail where model_id =@model_id ) exec('insert into INV_Stock_Detail (model_id,Quantity_In_Stock) values (@model_id,0)')

(the exec part)

Need to use sp_executeSql with relevent variables or change to

exec('insert into INV_Stock_Detail (model_id,Quantity_In_Stock) values (' + CAST(@model_id AS VARCHAR) + ',0)')

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-06 : 09:11:09

Or better yet get rid of the dynamic call. It doesn't look like you need it..


if not exists(select * from INV_Stock_Detail where model_id =@model_id )
insert into INV_Stock_Detail (model_id,Quantity_In_Stock) values (@model_id,0)

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources