Msg 217, Level 16, State 1, Procedure tr_TriggerTest_Sample, Line 5 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Vuyiswamb
Posted by in Error and Solution category on for Advance level | Views : 9831 red flag

I always Blog about the Errors i got that are no Common, but i realised that few people might know about my Bloggs, so i decided to write an Article based on the Error and Solution from uncommon errors in the Programming World.
Introduction
When you Create Triggers for your Table , you might find that the number of triggers might be more than one and they might be doing the same thing to the same table but for different purposes. This will cause a infinite loop and give you the following error



Msg 217, Level 16, State 1, Procedure tr_TriggerTest_Sample, Line 5
Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32).

Problem

[code]
Alter TRIGGER tr_mtm_subj_strm_actv
ON mtm_subj_strm_actv FOR DELETE
AS
DELETE mtm_subj_strm_actv
WHERE STRM NOT IN (SELECT ID FROM  tbl_subj_strm)
[/code]

[code]
Alter TRIGGER tr_UPDATE_MTM_SUBJ_STRM_ACTV_TABLE
ON  MTM_SUBJ_STRM_ACTV FOR DELETE
AS
DELETE MTM_SUBJ_STRM_ACTV
WHERE ACTV NOT IN (SELECT ID FROM TBL_ACTV)
[/code]

Solution
To resolve this , you can use TRIGGER_NESTLEVEL() Function in all your Triggers like this

[code]
Alter TRIGGER tr_mtm_subj_strm_actv
ON mtm_subj_strm_actv FOR DELETE
AS
begin
IF TRIGGER_NESTLEVEL() > 1
     RETURN

DELETE mtm_subj_strm_actv
WHERE STRM NOT IN (SELECT ID FROM  tbl_subj_strm)
end
[/code]


Alter TRIGGER tr_UPDATE_MTM_SUBJ_STRM_ACTV_TABLE
ON  MTM_SUBJ_STRM_ACTV FOR DELETE
AS
begin
IF TRIGGER_NESTLEVEL() > 1
     RETURN


DELETE MTM_SUBJ_STRM_ACTV
WHERE ACTV NOT IN (SELECT ID FROM TBL_ACTV)

END

check the Bold parts
 
Conclusion

Thank you for reading my Article


Vuyiswa Maseko

Page copy protected against web site content infringement by Copyscape

About the Author

Vuyiswamb
Full Name: Vuyiswa Maseko
Member Level: NotApplicable
Member Status: Member,MVP,Administrator
Member Since: 7/6/2008 11:50:44 PM
Country: South Africa
Thank you for posting at Dotnetfunda [Administrator]
http://www.Dotnetfunda.com
Vuyiswa Junius Maseko is a Founder of Vimalsoft (Pty) Ltd (http://www.vimalsoft.com/) and a forum moderator at www.DotnetFunda. Vuyiswa has been developing for 16 years now. his major strength are C# 1.1,2.0,3.0,3.5,4.0,4.5 and vb.net and sql and his interest were in asp.net, c#, Silverlight,wpf,wcf, wwf and now his interests are in Kinect for Windows,Unity 3D. He has been using .net since the beta version of it. Vuyiswa believes that Kinect and Hololen is the next generation of computing.Thanks to people like Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject), Sheo Narayan (.Netfunda),Rajesh Kumar(Microsoft) They have made vuyiswa what he is today.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)