Max stored proc, function, trigger or view nest level exceeded 32

Problem

I am trying to resolve an issue with triggers. We use them for things like automatically setting the update date and time on some tables. Recently we added a second trigger to a table and now we are getting the error “Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)”. For the time being we dropped that latest trigger. Can you help us to resolve this?

Solution

As a first step let’s review an example which will duplicate your problem. We’ll create a single table with two triggers. We will use the following table to test our triggers:

CREATE TABLE dbo.tbl_TriggerTest
(
    pk_TriggerTest INT  IDENTITY
  , Comment  NVARCHAR(256)
  , TriggerSample NVARCHAR(256)
  , Created  DATETIME DEFAULT GETDATE()
  , Updated  DATETIME DEFAULT GETDATE()
)

Now let’s create a trigger that sets the Updated column in our table to the current date and time by using the GETDATE() function:

CREATE TRIGGER dbo.tr_TriggerTest_Footprint
ON dbo.tbl_TriggerTest
AFTER UPDATE 
AS
  UPDATE dbo.tbl_TriggerTest
  SET  Updated = GETDATE()
  FROM  dbo.tbl_TriggerTest t 
  INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest

The above trigger will execute whenever there is an update to the tbl_TriggerTest table. The trigger references the inserted table which is a pseudo-table available to a trigger. An update is really handled as a delete followed by an insert. There is also a deleted pseudo table that is available to a trigger. The deleted table contains the rows that were updated with their values before the update; the inserted table contains the rows that were updated with their new values.

To test the trigger, we will run the following script to insert a row into our sample table then perform an update:

INSERT dbo.tbl_TriggerTest (Comment) 
VALUES ('X')
UPDATE dbo.tbl_TriggerTest 
SET Comment= 'This is a comment'

The above script runs successfully and the trigger updates the Comment column value to ‘This is a comment’.

Now we will create another trigger on our table to reproduce the trigger error:

CREATE TRIGGER dbo.tr_TriggerTest_Sample
ON dbo.tbl_TriggerTest
AFTER UPDATE 
AS
  UPDATE dbo.tbl_TriggerTest
  SET  TriggerSample = 'updated ' + CAST(GETDATE() AS NVARCHAR(20)) 
  FROM  dbo.tbl_TriggerTest t 
  INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest

This trigger updates the TriggerSample column in our table. When we rerun the test script above we get the following error message:

Msg 217, Level 16, State 1, Procedure tr_TriggerTest_Sample, Line 5
Maximum stored procedure, function, trigger, or view nesting level 
exceeded (limit 32).

Notice that both triggers are updating the table that causes the triggers to fire. Essentially what is happening is the update statements in the trigger are causing the triggers to fire again and this keeps going until the nesting level is exceeded. The resolution to this problem is to check the nesting level in the trigger and only execute the update statement one time. To do this you can use the function TRIGGER_NESTLEVEL.

Alter both of the triggers as follows:

ALTER TRIGGER dbo.tr_TriggerTest_Footprint
ON dbo.tbl_TriggerTest
AFTER UPDATE 
AS
BEGIN
  IF TRIGGER_NESTLEVEL() > 1
     RETURN
     
  UPDATE dbo.tbl_TriggerTest
  SET  Updated = GETDATE()
  FROM  dbo.tbl_TriggerTest t 
  INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
END
GO
  
ALTER TRIGGER dbo.tr_TriggerTest_Sample
ON dbo.tbl_TriggerTest
AFTER UPDATE 
AS
BEGIN
  IF TRIGGER_NESTLEVEL() > 1
     RETURN
     
  UPDATE dbo.tbl_TriggerTest
  SET  TriggerSample = 'updated ' + CAST(GETDATE() AS NVARCHAR(20)) 
  FROM  dbo.tbl_TriggerTest t 
  INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest  
END
GO

If you run the above test script again, you will see that the error is now resolved. The change to the above triggers is to test the nesting level and only execute the code in the trigger when the nesting level is 1. The nesting level will be 1 the first time the trigger is called as a result of an update statement. Each successive time the trigger is called as a result of the same update statement, the nesting level will increment by 1.

There is another way to resolve this problem. The SQL Server configuration option “nested triggers” determines whether an action performed in a trigger can cause the trigger to fire again. The default value for “nested triggers” is 1, which allows the behavior we saw in our sample. You could change “nested triggers” to zero and disable trigger nesting.

Next Steps

Take a look at the TRIGGER_NESTLEVEL function in SQL Server Books Online to review the complete details on this useful function.
There are a number of other MSSQLTips on this site related to triggers; search on TRIGGER and you will get all sorts of useful information.
Many people are reluctant to use triggers. However, they are useful in some instances and can be used if you are careful.
Take a look at the sp_configure system stored procedure in Books Online to get the details on changing the “nested trigger” configuration option.

Resources

Community

Subscribe