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

我有一个带有year列的table1并且如果应用程序更新了该年列，那么我需要使用同一年的年表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 在触发器内。使用插入的伪表来获取之后值。还请记住，更新可能会影响多行。

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 语句替换为

So replace your current update statement with

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

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