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

Get Microsoft SQL Server 2012 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.