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

    Question

  • I have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?
    Tuesday, October 16, 2007 3:24 PM

Answers

All replies

  • YOu could check the


    TRIGGER_NESTLEVEL

     
    and only process the delete if the Nestlevel for the specified trigger is below your threshold.

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Tuesday, October 16, 2007 9:14 PM
    Moderator
  • Its like endless loop. One Delete trigger other and vice versa... so there is no end to it. This is a server level setting to avoid idefinite loop..

     

    From BOL

    nested triggers Option

    Use the nested triggers option to control whether a trigger can cascade (perform an action that initiates another trigger that initiates another trigger, and so on). When nested triggers is set to 0, triggers cannot cascade. When nested triggers is set to 1 (the default), triggers can cascade to as many as 32 levels.

     

    Also read about RECURSIVE_TRIGGERS in BOL

     

     

    Madhu

    Wednesday, October 17, 2007 1:54 AM
    Moderator
  • Hi,

    I had the same problem before.What you need to do is to turn off recursive triggers,functions,view or store procedures which cause the problem

    For trigger for example Go to SSMS,open the table in question,open trigger folder
    and right click the trigger that cause the problem and choose disable.
    You should be good to go


    Good Luck!

    Talley
    Blog:http://talleyblogs.blogspot.com/
    • Edited by Talley37 Thursday, August 13, 2009 6:33 PM
    Thursday, August 13, 2009 6:10 PM