Microsoft® SQL Server® 2012 Bible

880

Part VII: Monitoring and Auditing

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

plete, 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 fi re.

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 fi red. Knowing these effects can

prevent confl icts with constraints, locking, and blocking on the affected tables.

Every transaction invokes various checks in the following order:

1. IDENTITY INSERT check.


2. Nullability constraint.


3. Data-type check.


4. INSTEAD OF trigger execution. If an INSTEAD OF trigger exists, then execution
   of the DML stops here. INSTEAD OF triggers are not recursive. (Recursive triggers
   are covered later .)

c36.indd 880c36.indd 880 7/31/2012 10:03:07 AM7/31/2012 10:03:07 AM

http://www.it-ebooks.info