TRIGGER INSTEAD OF Implement logical deletes

INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them. Actual insert, delete or update operation do not occur at all. However they have their associated inserted and deleted tables simulating the DML operation. INSTEAD OF triggers do not work in a recursive manner. Even if you update the same table inside the INSTEAD OF trigger, the trigger will not be invoked a second time. So INSTEAD OF triggers can be used to avoid recursion.


INSTEAD OF trigger may be attached for delete operations. 


The example below demonstrates simple logical delete implementations:


CREATE TRIGGER trigg_MyTable on MyTable
INSTEAD OF DELETE
AS
IF @@ROWCOUNT = 0
RETURN
UPDATE tbl
SET Deleted = '1'
FROM MyTable tbl 
JOIN deleted d ON d.PK_ID = tbl.PK_ID