如何使用更新触发器更新另一个表? [英] 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.

我有一个带有年份列的表 1，如果应用程序更新了该年份列，我需要用同年的年份更新表 2.

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

推荐答案

您没有在触发器内部引用 table1.使用 inserted 伪表来获取after"值.另请记住，更新可能会影响多行.

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.

所以用

UPDATE table2
SET    table2.annualyear = inserted.intannualyear
FROM   table2
       JOIN inserted
         ON table2.id = inserted.id  

这篇关于如何使用更新触发器更新另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！