How do I make a trigger that only affects the row

问题:

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.

Here's what I have now:

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?

回答1:

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 

But if ColumnB is always a copy of ColumnA, why not create a Computed column instead?

Using the inserted and deleted Tables

回答2:

There is a special inserted table available in triggers that will contain the "after" version of rows impacted by an INSERT or UPDATE operation. Similarly, there is a deleted table that will contain the "before" version of rows impacted by an UPDATE or DELETE operation.

So, for your specific case:

UPDATE t
    SET t.columnB = t.columnA
    FROM inserted i
        INNER JOIN table t
            ON i.PrimaryKeyColumn = t.PrimaryKeyColumn