Recursive triggers in SQL Server

In this blog we’ll look at Recursive triggers in SQL Server. Triggers are said to be recursive/nested when a trigger on a table calls another trigger on same or different table and so on. The total numbers of recursive calls that can be made are limited to 32.

Recursion can be enabled or disabled as shown below

sp_configure 'nested_triggers',1
GO
RECONFIGURE 
GO
-- 
ALTER DATABASE AdventureWorks2014
SET RECURSIVE_TRIGGERS ON

GO
-- Disable Recursion
sp_configure 'nested_triggers',0
GO
RECONFIGURE 
GO
ALTER DATABASE AdventureWorks2014
SET RECURSIVE_TRIGGERS OFF

Let’s see an example of recursive triggers. The below query creates two table tblone and tbltwo and two triggers trgone on tblone and trgtwo on tbltwo.

CREATE TABLE tblone(Sno INT IDENTITY,col1 char(1))
go
CREATE TABLE tbltwo(Sno INT IDENTITY,col2 char(1))
go

CREATE TRIGGER trgone
    ON [dbo].tblone
    FOR INSERT
    AS
    BEGIN
    SET NOCOUNT ON
	INSERT INTO tbltwo(col2)
	SELECT col1 FROM inserted 
    END

	GO
CREATE TRIGGER trgtwo
    ON [dbo].tbltwo
    FOR INSERT
    AS
    BEGIN 
	INSERT INTO tbltwo(col2)
	SELECT 'A'
    SET NOCOUNT ON
    END

The trigger trgone is an insert trigger on tblone which inserts values from inserted table into tbltwo. The trigger tbltwo is another insert trigger on tbltwo. Thus, the trigger trgone will initiate the trgtwo on tbltwo. Let’s insert a value in trgone table.

As shown in above snapshot, an insert on tblone initiates trgone which in turn initiates trgtwo on tbltwo. If nested triggers are off, trgone will fire however trgtwo won’t and no error will be thrown.

Triggers nested level can be checked using TRIGGER_NESTLEVELfunction as shown below.

IF ((
SELECT TRIGGER_NESTLEVEL()) > 32)
RETURN

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook