How to use update trigger to update another table?

sql sql-server-2008 triggers sql-update
Spafa9 picture Spafa9 · Mar 9, 2011 · Viewed 73k times · Source

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

Answer

Martin Smith picture Martin Smith · Mar 9, 2011

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

Related questions

How can I edit values of an INSERT in a trigger on SQL Server?

I have the table Tb ID | Name | Desc ------------------------- 1 | Sample | sample desc I want to create a trigger on INSERT that will change the value of the inserting Desc, for example: INSERT INTO Tb(Name, Desc) VALUES ('x', 'y') Will …

Read More
How to Generate Scripts For All Triggers in Database Using Microsoft SQL Server Management Studio

I'd like to generate an SQL Script that contains the SQL to create all of the triggers that exist in our database. The triggers were added directly via the SSMS query pane so, there is currently no source other than …

Read More
How do I prevent a database trigger from recursing?

I've got the following trigger on a table for a SQL Server 2008 database. It's recursing, so I need to stop it. After I insert or update a record, I'm trying to simply update a single field on that table. Here's …

Read More