Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 219

Execution Order
The AFTER and INSTEAD OF triggers have an order of execution, which you need to be aware of. The
order is: INSTEAD OF triggers, Constraint checks, AFTER triggers. Because of this order, whenever a
constraint check fails, any subsequent AFTER triggers will not run.

Also some notes about INSTEAD OF triggers. Because they occur before any constraint checking occurs
an INSTEAD OF trigger could be used as type of pre-processor. Any items that might fail the constraint
checks could be fixed in the INSTEAD OF trigger. Another aspect of INSTEAD OF triggers is that if the
trigger executes statements against the table the trigger is defined for, the trigger won't fire again. If it
did, SQL Server would get into an infinite calling loop.

Multiple Triggers
A table can have multiple AFTER triggers. If you wanted to you could define 3 UPDATE triggers for a
single table, as long as each trigger had a unique name.

A table can only have one INSTEAD OF trigger for each type: UPDATE, DELETE, and INSERT.

DDL
A DDL trigger executes whenever a DDL event occurs. DDL event groups include operations: CREATE,
ALTER, and DROP. These actions can occur on many database objects such as tables, indexes, and pro-
cedures. See the BOL topic ''DDL Events for Use with DDL Triggers'' for a complete list of all the events
that can be used to define this type of trigger.

Scope
DDL triggers have two different scopes. They are: database and current server. Database-level scope
triggers fire from actions that modify the database schema. For example, a CREATE TABLE statement
might fire a DDL trigger. These triggers are stored in the database.

Sever-level scope triggers fire from server changes. These triggers are stored in the master database.

DDL Trigger Usage
Some of the reasons you may want to use a DDL trigger include:

Preventing or logging changes to your database. You may need to log or even prevent changes
made to a database. A DDL trigger can aid in that goal.

❑

Enforcing standards. You may have standards such as naming conventions or comment
requirements for your database objects. DDL triggers can enforce them by applying those stan-
dards to new objects, such as tables, views, or stored procedures.

❑

NestedTriggers
Triggers can be nested together to create a trigger chain. Triggers can be nested up to 32 levels deep.
Any trigger in the chain that starts an infinite loop of firing triggers will terminate once the 32 limit is
exceeded. These are enabled by default. However, you can change the setting using the sp_configure
command. For nested triggers the syntax would be:

Sp_Configure 'nested triggers', 0|1