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', 1RECONFIGURE
To turn them off, type the following:
EXEC sp_configure 'nested triggers', 0RECONFIGURE
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 '', 'recursive triggers'
Use this command to turn them:
EXEC sp_dboption '', 'recursive triggers', 'true'
Use this command to turn them off:
EXEC sp_dboption '', 'recursive triggers', 'false'