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.

SQL SERVER - Nested Triggers - Trigger Calling Another Trigger Error nestedtrigger-800x241

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)

, ,
Previous Post
SQL SERVER – 2017 – Script to Clear Procedure Cache at Database Level
Next Post
SQL SERVER – Unable to Repair – SQL_Telemetry_Repair_Startup_Cpu64 gives error – The Service Cannot Be Started

Related Posts

2 Comments. Leave new

  • I would like to show below script. You can test this script to solve error message Msg 217.
    Thanks.

    ALTER TRIGGER dbo.FirstTrigger
    ON dbo.FirstTable
    FOR INSERT
    AS
    BEGIN
    IF TRIGGER_NESTLEVEL() > 1
    RETURN

    INSERT INTO dbo.SecondTable (InsertTime)
    VALUES(GETDATE());
    END
    GO
    —————————————————————–
    ALTER TRIGGER dbo.SecondTrigger
    ON dbo.SecondTable
    FOR INSERT
    AS
    BEGIN
    IF TRIGGER_NESTLEVEL() > 1
    RETURN

    INSERT INTO dbo.FirstTable (InsertTime)
    VALUES(GETDATE());
    END
    GO

    —————————————————————–

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

    SELECT * FROM FirstTable
    SELECT * FROM SecondTable

    Reply

Leave a Reply

Menu