Thursday, December 15, 2011

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
SET Deleted = '1'
FROM MyTable tbl 
JOIN deleted d ON d.PK_ID = tbl.PK_ID

No comments:

Post a Comment