Msg 217: Nested Triggers Exceed the 32 Level Limit

Msg 217 appears when triggers fire each other until the nesting limit of 32 is reached. A reader emailed me a question about nested triggers. I answered by email, and I’m writing it up here because other readers will have the same question.

The Error

This is the message SQL Server returns when the chain of triggers goes too deep:

Msg 217, Level 16, State 1, Procedure SecondTrigger, Line 5 [Batch Start Line 20]
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

What the Reader Sent

The reader described a trigger that calls another trigger. I built an example. First, create two sample tables.

CREATE TABLE FirstTable (ID INT IDENTITY(1,1), InsertTime DATETIME);
CREATE TABLE SecondTable (ID INT IDENTITY(1,1), InsertTime DATETIME);
GO

Next, create two AFTER INSERT triggers. Each trigger inserts a row into the table that holds the other trigger. That creates a loop.

CREATE TRIGGER dbo.FirstTrigger
ON dbo.FirstTable
FOR INSERT
AS
INSERT INTO dbo.SecondTable (InsertTime)
VALUES(GETDATE());
GO
CREATE TRIGGER dbo.SecondTrigger
ON dbo.SecondTable
FOR INSERT
AS
INSERT INTO dbo.FirstTable (InsertTime)
VALUES(GETDATE());
GO

Now insert a row into the first table and see what happens.

INSERT INTO FirstTable (InsertTime)
VALUES (GETDATE())
GO

Why It Happens

SQL Server doesn’t allow more than 32 nesting levels for triggers. After 32 executions of the triggers, it raises Msg 217 and doesn’t run the insert at all. The nested triggers server option was already enabled for this test, and that is what lets one trigger fire another.

How to Check

Select from both tables. Both are empty, because SQL Server never completed the insert.

SELECT * FROM FirstTable;
SELECT * FROM SecondTable;
GO

How to Prevent It

Before you add a trigger that writes to another table, check whether that table has a trigger that writes back. Two triggers that insert into each other’s tables always form a loop.

Msg 217 is not a random failure, it is SQL Server stopping a loop at level 32.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.