如何制作仅影响已更新/插入的行的触发器?

2022-01-01 00:00:00 sql-server triggers

我有一个包含两列的表格，我需要其中一列 (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?

使用插入和删除的表

相关文章