Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
 Posted - 2007-12-26 : 06:50:26
|
| Hi, I wanted to know, in what order does Triggers get fire? So, I created Tab1 and defined two triggers on it, i.e. Trg_One and Trg_Two, both are AFTER INSERT.when I tried to execute insert command on Tab1 I got an error, ie.Msg 217, Level 16, State 1, Procedure Trg_One, Line 5Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).Can anybody tell me, how to overcome this problem, rather I would like to know how to set the NestLevel and what is the max limit of NestLevel?Also I will be very thankful, if anybody will tell me, in what order does the triggers get fired?Thanks in advance,Mahesh |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-12-26 : 06:56:57
|
| I believe yours is the case of infinite loop of indirect recursive triggers. For example, Trigger A does some action which fires Trigger B which again does some action which fires Trigger A and so on. SQL Server will continue in this fashion till max nesting level is reached which is 32.You can't control firing order of each trigger but you can set which trigger should be first and which should be last using sp_settriggerorder.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-12-26 : 07:01:43
|
quote:
Originally posted by harsh_athalye
I believe yours is the case of infinite loop of indirect recursive triggers. For example, Trigger A does some action which fires Trigger B which again does some action which fires Trigger A and so on. SQL Server will continue in this fashion till max nesting level is reached which is 32.You can't control firing order of each trigger but you can set which trigger should be first and which should be last using sp_settriggerorder.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
Agree, but Harsh I need to set the NestLevel to some upper limit coz currently my NestLevel is 0 [Zero]. I have set the trigger firing action to First and LastThanks,Mahesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-26 : 07:06:59
|
| Refer this. Might be of help:-[url]http://www.eggheadcafe.com/software/aspnet/31374640/trigger-nesting-level-exc.aspx[/url] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-12-26 : 07:35:26
|
| and can yoo post your trigger code?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Nested Trigger