Recursive Triggers

The recursive triggers were introduced in version 7.0. If a trigger modifies the same table where the trigger was created, the trigger will not fire again unless recursive triggers is turned on. Recursive triggers is a database option, which is off by default.

The first command of the following example checks the setting of recursive triggers for the Pubs database, and the second sets recursive triggers to TRUE:

EXEC sp_dboption pubs, 'recursive triggers' 
EXEC sp_dboption pubs, 'recursive triggers', TRUE

If you turn off nested triggers, recursive triggers is automatically disabled, regardless of how the database option is set. The maximum nesting level for recursive triggers is the same as for nested triggers: 32 levels.

Recursive triggers should be used with care. It is easy to create an endless loop, as shown in Listing 29.5, which creates a recursive trigger on a new test table in the Pubs database.

Listing 29.5 Error Message Returned for an Endless Loop with Recursive Triggers

EXEC sp_configure 'nested triggers', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_dboption pubs, 'recursive triggers', TRUE
CREATE TABLE rk_tr_test (id int IDENTITY)
GO
CREATE TRIGGER rk_tr ON rk_tr_test FOR INSERT
AS INSERT rk_tr_test DEFAULT VALUES
GO
INSERT rk_tr_test DEFAULT VALUES

Server: Msg 217, Level 16, State 1, Procedure rk_tr, Line 2
Maximum stored procedure nesting level exceeded (limit 32).

The recursion described thus far is known, more explicitly, as direct recursion. Another type of recursion exists as well, and it is known as indirect recursion. With indirect recursion, a table with a trigger fires an update to another table, and this table, in turn, causes an update to happen to the original table on which the trigger fired. This causes the trigger on the original table to fire again.

With indirect recursion, setting the recursive triggers database setting to false does not prevent the recursion from happening. The only way to prevent this type of recursion is to set the nested triggers setting to false, which, in turn, prevents all recursion.