SQL Server triggers fire once per data-modification operation, not once per affected row. This may seem to be a limitation, but developing set-based triggers actually helps ensure clean logic and fast performance.
Triggers may be created for the three data-modification commands: INSERT, UPDATE, and DELETE.
SQL Server has two kinds of transaction triggers: instead of triggers and after triggers. They differ in their purpose, timing, and effect, as detailed in Table 36.1.
|Instead of Trigger||After Trigger|
|DML statement||Simulated but not executed||Executed, but can be rolled back in the trigger|
|Timing||Before PK and FK constraints||After the transaction is complete, but before it is committed|
|Number per table event||One||Multiple|
|May be applied to views?||Yes||No|
|Nested?||Depends on server option; however, Nested INSTEAD OF Triggers will always fire.||Depends on server option|
|Recursive?||Only for INSTEAD OF Triggers||Depends on database option|
Triggers affect the transactional state in which they're fired. Knowing these effects can prevent conflicts with constraints, locking, and blocking on the affected tables.
Every transaction invokes various checks in the ...