522
Part V: Enterprise Data Management
Nested Triggers
A trigger is a small stored procedure executed on an insert, update, or delete opera-
tion on a table. Triggers are nested when a trigger performs an action that initiates another
trigger, which can initiate another trigger, and so on. Triggers can be nested up to 32
levels. You can use the nested triggers server confi guration option to control whether
AFTER trigger can be nested triggers.In Management Studio, you can set the nested trigger option by selecting True
(default) or False in the Allow Triggers to Fire Others option on the Server Properties
Advanced tab (refer to Figure 19-10).To turn nested triggers OFF in code, do the following:EXEC sp_configure 'nested triggers', 0;
RECONFIGURE;INSTEAD OF triggers can be nested regardless of the setting of this option.Recursive Triggers
If the code in the trigger inserts, updates, or deletes the same table again, then the trigger
causes itself to be executed again. Recursion can also occur if the code in the trigger fi res
and performs an action that causes a trigger on another table to fi re. This second trigger
performs an action that causes an update to occur on the original table, which causes the
original trigger to fi re again. Recursive behavior is enabled or disabled by the recursive
trigger database option. By default, the RECURSIVE_TRIGGERS option is set to off.In Management Studio, the recursive triggers option can be enabled by selecting True in
the Recursive Triggers Enabled option in the Database Properties Options tab (refer to
Figure 19-2).To set the recursive triggers option on in the AdventureWorks2012 sample database in T-SQL
code, do the following:ALTER DATABASE AdventureWorks2012 SET RECURSIVE_TRIGGERS ON;The server property nested triggers and the database property recursive triggers are
often confused with each other. Refer to Chapter 36, “Creating Triggers,” for a complete explanation,
including coverage of how triggers can call other triggers and how these properties control trigger behavior.Database-State-Confi guration Properties
The database-state-confi guration properties, as shown in Table 19-12, are available in SQL
Server. These confi gurations are mostly used when a DBA performs maintenance on the
database.c19.indd 522c19.indd 522 7/30/2012 5:43:12 PM7/30/2012 5:43:12 PM
http://www.it-ebooks.info