如何使用更新触发器来更新另一个表？
我是触发器新手，想要在更新列时创建触发器并使用该值更新另一个表。
我的表 1 包含年份列，如果应用程序更新该年份列，我需要使用同年的年份更新表 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
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您没有在触发器内引用
table1。使用inserted伪表获取“after”值。另请记住，更新可能会影响多行。因此，将当前的
update语句替换为You don't reference
table1inside the trigger. Use theinsertedpseudo table to get the "after" values. Also remember that an update can affect multiple rows.So replace your current
updatestatement with如果涉及到intannualyear列，则只需更新table2中的记录即可。此外，这是 Martin 展示的跨两个表的替代 UPDATE 语法
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
根据 这个问题，如果只有一个“下游”表，则另一种选择具有正确定义的外键关系将是级联更新。
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.
为了补充上述答案，如果您必须检查多个列，您可以在插入和删除之间使用 INNER JOIN，或多次 UPDATE() 调用：
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: