CODEWITHSUNDEEP
sqlsql-server-2008triggerssql-update
Spafa9
Spafa9

Reputation: 762

How to use update trigger to update another table?

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

Upvotes: 30

Views: 98291

Answers (4)

Bernardo Dal Corno
Bernardo Dal Corno

Reputation: 2088

To supplement the above answers, if you have to check more than one column you can use a INNER JOIN between inserted and deleted, or several UPDATE() calls:

IF ( UPDATE(Col1) OR UPDATE(Col2) ) BEGIN ...

Upvotes: 0

Bilbo
Bilbo

Reputation: 380

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.

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

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

Upvotes: 9

Martin Smith
Martin Smith

Reputation: 452947

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

Upvotes: 39

Related Questions