Nested and Recursive Triggers in SQL Server

By: Daniel Farina

Overview

In this chapter I will introduce the concepts of nested and recursive triggers and we will see how to create a nested trigger in SQL Server.

A Word about Nested Triggers and Trigger Recursion

When a trigger, during its execution, fires another trigger it's said to be a nested trigger. This could happen in both DML and DDL type triggers. There is a particular case of nested triggers, recursive triggers on which any of the triggers on the nesting chain are fired twice. What I want to emphasize is that nested triggers are not necessarily recursive triggers.

In order to use nested triggers in your SQL Server instance, you must configure it to allow them by setting the "nested triggers" configuration option to 1 as shown in the code section below.

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'nested triggers', 1;  
GO  
RECONFIGURE;  
GO  

At this point you have allowed nested triggers in your instance, but if you want to enable recursive triggers you need these additional steps.

To allow server trigger recursion you need to set the "server trigger recursion" option to 1.

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'server trigger recursion', 1;  
GO  
RECONFIGURE;  
GO  

To allow recursive triggers in a given database you have to set the database option RECURSIVE_TRIGGERS to ON as follows.

ALTER DATABASE SampleDB SET RECURSIVE_TRIGGERS ON;  
GO

Something to keep in mind is that to avoid the risk of infinite loops, SQL Server has a maximum of 32 levels of nesting (and therefore recursion).

Sample Test Scenario

Now let's create a nested trigger using the code from the previous chapter of this tutorial. Below is the code with the addition of table NestingTestLog.

CREATE TABLE NestingTest
(
    NestingTestID INT IDENTITY(1, 1),
    Test INT NULL
);
 
CREATE TABLE NestingTestLog
(
    NestingTestLogID INT IDENTITY(1, 1),
    NestingTestID INT NULL,
    Test INT NULL,
    Operation VARCHAR(15) NOT NULL,
    OpDate DATETIME NOT NULL
);
GO

Now take a look at what I changed in the code for the TR_IUD_NestingTest trigger. Besides inserting into NestingTestLog table I removed the print @Operation variable. Instead, I am printing a message at the beginning and end of the trigger's code that will allow us to see clearly when the trigger starts and ends execution.

CREATE OR ALTER TRIGGER TR_IUD_NestingTest ON NestingTest
FOR INSERT, UPDATE, DELETE
AS
DECLARE @Operation VARCHAR(15)
PRINT 'BEGIN TR_IUD_NestingTest execution...'
IF EXISTS (SELECT 0 FROM inserted)
BEGIN
   IF EXISTS (SELECT 0 FROM deleted)
   BEGIN 
      SELECT @Operation = 'UPDATE'
   END ELSE
   BEGIN
      SELECT @Operation = 'INSERT'
   END
ENDELSE 
BEGIN
   SELECT @Operation = 'DELETE'
END 
 
INSERT INTO dbo.NestingTestLog ( NestingTestID, Test, Operation, OpDate )
SELECT D.NestingTestID, D.Test, @Operation, GETDATE()
FROM Deleted D;
 
PRINT 'END TR_IUD_NestingTest execution...'
GO

Additionally, on the dbo.NestingTestLog table let's create the TR_IUD_NestingTestLog trigger. In order to make things simple, this trigger won't do anything but print a message.

CREATE OR ALTER TRIGGER TR_IUD_NestingTestLog ON dbo.NestingTestLog
FOR INSERT, UPDATE, DELETE
AS
 
PRINT 'BEGIN TR_IUD_NestingTestLog execution...'
 
PRINT 'Here goes some processing stuff'
 
PRINT 'END TR_IUD_NestingTestLog execution...'

In the next code section you will find a script to test these nested triggers.

INSERT INTO dbo.NestingTest (Test) VALUES (0);
 
UPDATE dbo.NestingTest SET Test = 1 WHERE NestingTestID = 1;
 
DELETE FROM dbo.NestingTest WHERE NestingTestID = 1;

As you can see on the next screen capture, I divided the output of each statement in rectangles with distinct colors. You will see that there are two "rows affected" outputs for each statement execution. The first represents the rows inserted in NestingTestLog table and the other tells the rows affected by the DML statement in the NestingTest table.

Additional Information

• Forcing Trigger Firing Order in SQL Server
• SQL Server System Configuration Values
• How to allow Ad hoc updates in SQL Server system catalogs
• Quickly identify non-default sp_configure values for a SQL Server instance