Error 217: Maximum stored procedure nesting level exceeded.

[jeannelc]

I have a table in SQL Server 7.0 that has a series of constraints and triggers attached that were created via Enterprise Manager. Each one is run separately, there is no explicit nesting. There are 2 check constraints and 5 triggers. The table also contains 8 foreign keys mapped to other tables.

The 5th trigger is as follows:

CREATE TRIGGER utr_report_last_update ON dbo.tbl_incident_report
FOR UPDATE
AS
UPDATE tbl_incident_report
SET last_update=getdate()  from tbl_incident_report a, inserted b
WHERE a.report_id=b.report_id

I have field called last_update (NOT NULL) with a default of getdate().

My problem is this:
When I attempt an insert on this table it fails with the following message:

Server: Msg 217, Level 16, State 1,
Procedure utr_report_last_update, Line 5
Maximum stored procedure nesting level exceeded (limit 32).

I use this same trigger with no problems with my other tables that have a last_update field. Why is it causing me problems on an INSERT when it is an UPDATE trigger?

 

[foxdev]

Try setting your Recursive Triggers off, as in:
[tt]sp_dboption 'MyDatabase', 'recursive triggers', 0[/tt]

Robert Bradley

http://www.foxdev.com/

 

[jeannelc]

Recursive trigger is off -- always was off -- this was the first thing I checked. I also tried disabling the other triggers. This did not alter the problem in any way.

All other triggers and constraints fire properly. This is the only one that causes any problems.

 

[multiplex77]

I also have the same problem. Is there any way to trace which triggers are causing the recursive problem?

For that matter, is there a way to print or list out all the triggers for each table instead of opening each one up individually?

Thanks for the help!

 

[tlbroadbent]

Run the following in the database to list the triggers.

Select TrgName=name, TblName=object_name(parent_obj)
From sysobjects
Where type='tr'
--Order by column 2, 1 or TblName, TrgName
Order By 2,1 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.