How to use update trigger to update another table?
Solution 1
You don't reference table1
inside the trigger. Use the inserted
pseudo table to get the "after" values. Also remember that an update can affect multiple rows.
So replace your current update
statement with
UPDATE table2
SET table2.annualyear = inserted.intannualyear
FROM table2
JOIN inserted
ON table2.id = inserted.id
Solution 2
You only need to update the records in table2 if the column intannualyear is involved. Also, this is an alternative UPDATE syntax across two tables from what Martin has shown
IF UPDATE(intannualyear)
UPDATE table2
SET annualyear = inserted.intannualyear
FROM inserted
WHERE table2.id = inserted.id
Solution 3
According to this question, if there's only one "downstream" table then another option with a properly defined foreign key relation would be Cascaded update.
Related videos on Youtube
Spafa9
Updated on July 09, 2022Comments
-
Spafa9 over 1 year
I am new to triggers and want to create a trigger on an update of a column and update another table with that value.
I have table1 with a year column and if the application updates that year column I need to update table 2 with the year the same year.
ALTER TRIGGER [dbo].[trig_UpdateAnnualYear] ON [dbo].[table1] AFTER UPDATE AS if (UPDATE (intAnnualYear)) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here Update table2 set AnnualYear = intAnnualYear where table2.ID = table1.ID END
-
Spafa9 over 12 yearsThank you worked great. Just for clarification for other people just starting out with triggers I found a good article to explain the inserted table. msdn.microsoft.com/en-us/library/ms191300.aspx