Microsoft SQL Server 2012 Bible

Trigger Basics

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.

Best Practice

For data integrity, sometimes a trigger is the best solution, but be aware of the potential performance impact. You should consider having business rules enforced by application code instead and only use triggers when this is not feasible.

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.

Table 36.1 Trigger Type Comparison

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

Transaction Flow

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 ...