SQL 如何使用更新触发器更新另一个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5252802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use update trigger to update another table?
提问by Spafa9
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.
我有一个带有年份列的 table1，如果应用程序更新该年份列，我需要用同年的年份更新表 2。
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
回答by Martin Smith
You don't reference table1inside the trigger. Use the insertedpseudo table to get the "after" values. Also remember that an update can affect multiple rows.
您不在table1触发器内部引用。使用inserted伪表获取“之后”值。还请记住，更新可能会影响多行。
So replace your current updatestatement with
因此，将您当前的update声明替换为
UPDATE table2
SET table2.annualyear = inserted.intannualyear
FROM table2
JOIN inserted
ON table2.id = inserted.id
回答by RichardTheKiwi
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
如果涉及到 inannualyear 列，则只需更新 table2 中的记录。此外，这是 Martin 展示的跨两个表的替代 UPDATE 语法
IF UPDATE(intannualyear)
UPDATE table2
SET annualyear = inserted.intannualyear
FROM inserted
WHERE table2.id = inserted.id
回答by Bilbo
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.
根据这个问题，如果只有一个“下游”表，那么具有正确定义的外键关系的另一个选项将是级联更新。
回答by Z. Khullah
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:
为了补充上述答案，如果您必须检查多列，您可以在插入和删除之间使用 INNER JOIN 或多个 UPDATE() 调用：
IF ( UPDATE(Col1) OR UPDATE(Col2) ) BEGIN ...
