SQL SERVER – Nested Triggers – Trigger Calling Another Trigger Error

Recently I have received a very interesting question in an email. The user wanted to know how will trigger behave when there are nested triggers. In an email, he described a scenario where trigger called another trigger. I really liked the question and immediately answered in the email as well. However, I believe many others may have the same question, so decided to write this blog post with an example.

First, let us 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, we will create two triggers. Each trigger is AFTER INSERT trigger. When they are triggered they attempt to insert data into another table creating the infinite loop. In simple words, we have created a loop where we have trigger inserting data into the table where another trigger is created.

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 let us insert the data into the first table and see what happens?

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

Can you guess the answer before reading further?

If you have guessed that it will give you an error, you are correct. SQL Server is smart enough to figure out that there is a loop of a trigger which is going to into long loop. Here is the error.

SQL Server does not allow more than 32 nesting levels for triggers. Once SQL Server identifies that there will be 32 execution of the triggers, SQL Server throws an error and will not execute the insert at all.

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).

If you select the data, you will notice that both the tables are empty as SQL Server has already identified there will be an error due to nested triggers.

SELECT * FROM FirstTable;
SELECT * FROM SecondTable;
GO

Please note that for this experiment I had already enabled nested trigger server configurations.

Reference: Pinal Dave (https://blog.sqlauthority.com)