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?
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..
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
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
- Edited by Talley37 Thursday, August 13, 2009 6:33 PM