Variables 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

Variables in trigger

Author

Topic

perimidt
Starting Member

4 Posts

Posted - 2014-05-19 : 06:13:36

Hi I am trying to update one table when records are inserted in another table.

I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.

My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:


ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as 
begin
declare @liter as decimal(28,6) 
declare @kundenr as int
declare @aktor as int

select @kundenr = CustNo from inserted
select @liter = NoInvoAb from inserted
select @aktor = [ActNo] from [Actor] where [Actor].[CustNo] = @kundenr


update [ActInf] 
	set Qty3 = Qty3 + @liter
where ActNo = @aktor
end

Any tips would be very appreciated.

Best regards
Per Erik Midtrød

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-19 : 08:09:37

[code]ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin

UPDATE b
SET Qty3 = Qty3 + i.NoInvoAb
FROM inserted i
INNER JOIN Actor a ON i.CustNo = a.CustNo
INNER JOIN [ActInf] b ON a.ActNo = b.ActNo

end[/code]

KH
[spoiler]Time is always against us[/spoiler]

perimidt
Starting Member

4 Posts

Posted - 2014-05-19 : 08:25:34

Thanks for the suggestion, but I still cannot get it update the table ActInf.

Best regards
Per Erik Midtrød

perimidt
Starting Member

4 Posts

Posted - 2014-05-19 : 08:35:26

Never mind, when I add a record manually in the database it works, apparently there is something that stops the trigger when data is added from the ERP-system which is supposed to do this.

quote:
Originally posted by perimidt

Thanks for the suggestion, but I still cannot get it update the table ActInf.

Best regards
Per Erik Midtrød

Best regards
Per Erik Midtrød

perimidt
Starting Member

4 Posts

Posted - 2014-05-19 : 09:40:11

To anyone interested:
Apparently the ERP-system doesn't insert all fields at once so by changing the trigger to update instead of insert khtans code work perfectly.

Best regards
Per Erik Midtrød

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources