CODEWITHSUNDEEP
sqlsql-servertriggers
Andrey
Andrey

Reputation: 1769

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

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.

Upvotes: 1

Views: 1708

Answers (1)

Ross Bush
Ross Bush

Reputation: 15185

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.

Upvotes: 1

Related Questions