我是新的触发器,并希望在更新列上创建触发器,并使用该值更新另一个表.
我有一个年份列的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
解决方法
您不在引用内引用table1.使用插入的伪表获取“after”值.还要记住,更新可能会影响多个行.
所以用以下替换你当前的更新语句
UPDATE table2
SET table2.annualyear = inserted.intannualyear
FROM table2
JOIN inserted
ON table2.id = inserted.id
