Friday, February 22, 2013

Recursive and Nested Triggers in TSQL

We would get this error message "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)".

I was told that turning off recursive triggers would fix it.  Likely the problem could better be solved by redesigning the triggers and such, but not authorized for that right now.

Recursive Triggers is an Option that can be set by going into SQL Management Studio and right clicking on the database and clicking Properties.  In the left pane click Options.  Sort the properties alphabetically and set the option Recursive Triggers Enabled = false.

You can also set this option by:
ALTER DATABASE databasenameSET RECURSIVE_TRIGGERS OFF

The option can also be set like this:

EXEC sp_dboption 'database-name', 'recursive triggers', 'FALSE'


It appears that in different places you may see the values OFF, false, and 0, which all mean the same thing.  The inverse would be ON, true, and 1.

Once I set this option it did not fix the problem.  I ended up finding that there is another setting similar to Recursive Triggers called Nested Triggers.  It is not in the Options section with Recursive Triggers.

Nested Triggers are turned off like this:

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Make sure you are logged into the database for which you need to set the setting.

I don't entirely understand the difference between these two settings yet, but my guess is that Recursive Triggers controls whether a trigger is allowed to fire itself and that Nested Triggers controls whether a trigger can fire other triggers.