Home » Tip Bank » Database Development » Database Administration

Language: SQL
Expertise: Intermediate

Nov 28, 2005

To check whether nested triggers are turned on or off in SQL Server, issue the following command in Query Analyzer:

EXEC sp_configure 'nested triggers'

If the run_value is 0 then the nested triggers are off, if the value is 1 then they are on.

Therefore, to turn on nested triggers, type the following command:

EXEC sp_configure 'nested triggers', 1
RECONFIGURE

To turn them off, type the following:

EXEC sp_configure 'nested triggers', 0
RECONFIGURE

As you can see, the nested trigger setting works on a server level.

On the other hand, the recursive trigger setting works on a database level, so those commands require you to specify a database. To check the status of the recursive setting, issue this command:

EXEC sp_dboption '<name of db>', 'recursive triggers'

Use this command to turn them:

EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'

Use this command to turn them off:

EXEC sp_dboption '<name of db>', 'recursive triggers', 'false'