Trigger throws error Maximum stored procedure, function, trigger, or view nesting level exceeded

Question

I have two triggers which look almost the same but with small differences

Trigger 1

create trigger tr_EligebilityCheckSRmonth
on dbo.Clients
after INSERT,UPDATE
as 

BEGIN
UPDATE Clients 
SET 
StatusID = 5
WHERE 
ClientID IN (Select ClientID
             from Clients c 
             join IncomeEligibility i 
                  on c.HshldSize = i.HshldSize
             where c.HshldSize= i.HshldSize 
                   and c.AnnualHshldIncome >= i.SeniorMo 
                   and StatusID in (1,2) 
                   and c.CategCode = 'SR' 
                   and MonthlyYearly ='month')
END

Trigger 2

create trigger tr_EligebilityCheckSRyear
on dbo.Clients
after INSERT,UPDATE
as 

BEGIN
UPDATE Clients 
SET 
StatusID = 5
WHERE 
ClientID IN (Select ClientID
             from Clients c 
             join IncomeEligibility i 
                  on c.HshldSize = i.HshldSize
             where c.HshldSize= i.HshldSize 
                   and c.AnnualHshldIncome >= i.SeniorMo 
                   and StatusID in (1,2) 
                   and c.CategCode = 'SR' 
                   and MonthlyYearly ='year')
End

when I submit insert ststamnet like this

INSERT INTO Clients (ClientID, LastName, FirstName, MidInitial, DOB, Address, Address2, City, Zip, Phone, CategCode, StatusID, Hispanic, EthnCode, LangID, ClientProxy, Parent, HshldSize, AnnualHshldIncome, MonthlyYearly, PFDs, WIC, Medicaid, ATAP, FoodStamps, AgencyID, RoutID, DeliveryNotes, AppDate, CertifiedDate, Notes)
VALUES (13542,'Test','Test',null,'1982-10-20','P.O. Box 5',null,'TEST',99999,'(907) 111-1111','SR',1, 0, 'W',1, null, null,3,1000000,'year',0,0, 1,  0,1,45, null,null,'2011-04-27', null,null);

it throws error

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

I was trying to combine them in one trigger and use IF else but have no susses. it works if I have only one of them in system but when i have two of them it throws error. How to fix this problem? I check other posts here but cannot find anything useful.

http://stackoverflow.com/questions/9523413/trigger-nesting-level-exceeded — Brian Webster, Mar 05 '13 at 03:48

score 1 · Answer 1 · answered Mar 05 '13 at 03:53

1

Issuing and UPDATE in the trigger, for the table the trigger is bound to will not do what you are expecting. Just because the update code is inside the trigger code does not mean that the update statement will not cause the trigger to fire over and over and over. If helpful you can get a cursor for UPDATED and DELETED representing records that were either updated or deleted.

answered Mar 05 '13 at 03:53

Ross Bush

14,648
2
32
55

It all depends. I would try to find a way to break the relationship up into stored procedures. If you must create a TRIGGER TABLEA FOR UPDATE Then it would be ok to perform inserts and deletes, unless there is a trigger on the same table for those actions. – Ross Bush Mar 05 '13 at 04:15
they are both work with the same table . also there is one more trigger on same table and it throwing same error if i try to do insert – Andrey Mar 05 '13 at 04:26
I think this post is related to your question and provides two ways to work around trigger looping and nesting. http://stackoverflow.com/questions/2237499/sql-server-trigger-loop – Ross Bush Mar 06 '13 at 19:32
I fix this problem but right now having other problem trigger doesn't work as it must be http://stackoverflow.com/questions/15242495/trigger-doesnt-work-as-must-be?noredirect=1#comment21493307_15242495 – Andrey Mar 06 '13 at 20:51

Trigger throws error Maximum stored procedure, function, trigger, or view nesting level exceeded

1 Answers1