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
beginIF TRIGGER_NESTLEVEL() > 1
RETURNDELETE 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
RETURNDELETE MTM_SUBJ_STRM_ACTV
WHERE ACTV NOT IN (SELECT ID FROM TBL_ACTV)
ENDcheck the Bold parts
Conclusion
Thank you for reading my Article
Vuyiswa Maseko