Tuesday 18 March 2014

What are differences between Instead of Triggers and After Triggers?

SQL Server 2012 has two kinds of transaction triggers (DML Triggers): instead of triggers and after triggers. They differ in their purpose, timing, and effect, as detailed:

Instead of Triggers
After Triggers
DML statement simulated but not executed.
DML statement Executed, but can be rolled back in trigger.
It will execute before PK and FK constraints.

If will execute after the transaction is complete, but before it is committed.
You can create it on View or Table.
You can create it only on Table.
Only one per table and per event.
You can have multiple.

No comments:

Post a Comment