Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

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

MSSQLTips author Ray Barley By:   |   Read Comments (8)   |   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: 3/23/2009


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, April 08, 2014 - 4:23:40 AM - Sunil Read The Tip

 

Great article. You saved my day! Thank you.


Tuesday, January 07, 2014 - 4:11:55 AM - Rashmi Read The Tip

Wonderful article! It solved my problem immediately!

Thanks a Lot!

Rashmi


Friday, July 19, 2013 - 9:36:55 AM - Zoltán Read The Tip

It solved my problem immediately!

Thank you so much,

Zoltán


Thursday, February 28, 2013 - 11:15:12 AM - Dietmar Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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!




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.