如何创建仅影响已更新/插入的行的触发器? [英] How do I make a trigger that only affects the row that was updated/inserted?
问题描述
我有一个包含两列的表,其中我需要一个( columnB
)来复制另一个列( columnA
)。因此,如果插入或更新了一行,我希望将 columnA
中的值复制到 columnB
中。
I have a table with two columns where I need one (columnB
) to be a copy of the other one (columnA
). So, if a row is inserted or updated, I want the value from columnA
to be copied to columnB
.
这是我现在拥有的:
CREATE TRIGGER tUpdateColB
ON products
FOR INSERT, UPDATE AS
BEGIN
UPDATE table
SET columnB = columnA
END
现在的问题是查询影响所有行,而不仅影响已更新或插入的行。
The problem now is that the query affects all rows, not just the one that was updated or inserted. How would I go about fixing that?
推荐答案
假设您有一个主键列 id
(并且应该有一个主键)加入插入的
表(使触发器能够处理多行):
Assuming you have a primary key column, id
, (and you should have a primary key), join to the inserted
table (making the trigger capable of handling multiple rows):
CREATE TRIGGER tUpdateColB
ON products
FOR INSERT, UPDATE AS
BEGIN
UPDATE table
SET t.columnB = i.columnA
FROM table t INNER JOIN inserted i ON t.id = i.id
END
但是,如果ColumnB始终是ColumnA的副本,为什么不创建是计算列?
But if ColumnB is always a copy of ColumnA, why not create a Computed column instead?
这篇关于如何创建仅影响已更新/插入的行的触发器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!