How to make a trigger that affects only the row that has been updated / inserted?

Question

I have a table with two columns where I need one ( columnB ) to be a copy of the other ( columnA ). So, if a row is inserted or updated, I want the value from columnA be copied to columnB .

Here is what I have now:

 CREATE TRIGGER tUpdateColB ON products FOR INSERT, UPDATE AS BEGIN UPDATE table SET columnB = columnA END 

Now the problem is that the query affects all rows, not just those that have been updated or inserted. How can i fix this?

Answer 1

Assuming you have a primary key column, id , (and you must have a primary key), join the inserted table (so that the trigger can handle 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 inserted and deleted tables

Answer 2

Triggers have a special inserted table that will contain the "after" version of the rows affected by the INSERT or UPDATE operation. Similarly, there is a deleted table that will contain the up-to- deleted version of rows affected by the 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