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'
