MS SQL Server 2008 changing inserted row value in trigger

Question

In Oracle i can change the row value by :new.column_name = new_value in insert/update trigger. How can I do the same in MS SQL 2008 Trigger?

Answer 1

Unlike Oracle, affected records are passed in sets to SQL Server triggers, referenced to as INSERTED and DELETED.

You will have to update the target table:

UPDATE   m
SET      column_name = @new_value
FROM     INSERTED i
JOIN     mytable m
ON       m.id = i.id

or, better, create an INSTEAD OF trigger.