Turning Recursive and Nested Triggers On and Off

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'

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.