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

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