Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Maximum stored procedure, function, trigger or view nesting level exceeded limit 32


By:   |   Read Comments (9)   |   Related Tips: More > Triggers

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



Last Update:






About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips


 



More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, July 18, 2016 - 3:11:46 AM - Jagdeep Mankotia Back To Top

Thanks Ray,

This examle is very usefull for me to understand and resolve the below error facing in one of my project:

 

Msg 217, Level 16, State 1, Procedure trg_update_time, Line 15

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


Tuesday, April 08, 2014 - 4:23:40 AM - Sunil Back To Top

 

Great article. You saved my day! Thank you.


Tuesday, January 07, 2014 - 4:11:55 AM - Rashmi Back To Top

Wonderful article! It solved my problem immediately!

Thanks a Lot!

Rashmi


Friday, July 19, 2013 - 9:36:55 AM - Zoltán Back To Top

It solved my problem immediately!

Thank you so much,

Zoltán


Thursday, February 28, 2013 - 11:15:12 AM - Dietmar Back To Top

I had exactly that problem and THAT was the solution. Thanks a lot!

 

Dietmar Hoffmann, BI Consultant


Saturday, February 11, 2012 - 7:48:59 AM - Ray Barley Back To Top

I agree - disabling the nested trigger level server option is a very bad idea.  As I read through the tip again, it strikes me that it was probably something I added at the last minute without really thinking through the consequences of the advice.  

Thanks for pointing it out.  


Friday, February 10, 2012 - 8:03:02 PM - Maurice Pelchat Back To Top

Disabling the nested trigger level server option is a very bad idea.  Some other application may depend on it.  Also if you implement a delete cascade using a trigger, it will break the cascade.  

The first delete will still activate a trigger, but if this one propagates the delete to another table and this table has a delete trigger, it won't activate leaving the database inconsistent.  One may argues that someone should use referential integrity to have delete cascade, but in some case you can, especially in the case of circular reference.

 


Monday, November 15, 2010 - 4:37:49 PM - Grateful Back To Top

This helped out a lot Mr. Barley. Thank you for the very concise answer and resolution!


Wednesday, July 15, 2009 - 2:29:34 PM - Dave Morrissey Back To Top

This is a terrific post that clearly describes how to fix the problem I was having.  I wound up leaving the original trigger intact because it belongs to an outside vendor.  I put the nest level test in my trigger and it worked like a charm!  this ends several hours of tracking down the problem after creating the trigger, similuating what our application was doing.  Our application was throwing a cryptic error, but it was not letting me see the actual SQL error message, otherwise it would have been quicker to get to this great post.   For anyone else going through this scenario, it is helpful to add a select statement to your trigger which outputs the values of all the pertinent fields to a custom log table, because the handling of triggered data is by definition event-driven.  This allows you to take a "snapshot" of the data in all its stages at the moment it is being updated. Again, great post!


Learn more about SQL Server tools