Maximum stored procedure, function, trigger, or view nesting level exceeded

(0) Share

Report

Posted on by Emily Ann

620

Has anyone had the problem that on the MultiCurrency table Dynamics MC40200 the update trigger to the new in GP 2010 DEX_ROW_TS produces the error message:

Maximum stored procedure, function, trigger, or view nesting level exceeded ?

The trigger is :

CREATE TRIGGER [dbo].[zDT_MC40200U] ON [dbo].[MC40200] AFTER UPDATE
AS
set nocount on

BEGIN
UPDATE dbo.MC40200 SET DEX_ROW_TS = GETUTCDATE()
FROM dbo.MC40200, inserted
WHERE MC40200.CURNCYID = inserted.CURNCYID

END
set nocount off

If so, how did you fix the problem?

Thank you

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Emily Ann 620 on at

Like (0)

Report

Sorry to bother the community. I found my own solution. The trigger should be set as 'INSTEAD OF UPDATE' not 'AFTER UPDATE'. Not sure how/why the trigger has not caused other companies a problem and been fixed. We have installed the latest hotfix KB2552044 to GP 2010 R2 . It is working now.

Was this reply helpful? Yes No
Suggested answer

Mohamed El-Tohamy 305 on at

Like (0)

Report

I tried to run the above query against Dynamics.db, i got invalid column name "DEX_ROW_TS" so I've created this column manually in Dynamics.dbo.MC40200 then re-run the utilities and it has been passed.

Was this reply helpful? Yes No
Suggested answer

Tim Foster 8,515 on at

Like (1)

Report

32 is the nesting level I have observed in SQL. This problem looks very similar to a problem we encountered during an upgrade. There was a GP table that we had applied a custom (non-GP) trigger on. The upgraded version of Dynamics GP the table had a new time stamp trigger. A user would add a record - our trigger fired, GP trigger fired, our trigger fired because GP updated the table, the GP trigger fired because our trigger updated the table....etc. until error. We had to put code on our trigger to detect the activity of the time stamp trigger and not update if the time stamp was the only column being updated.

SQL Profiler can help you determine what's going on. There is a KB for creating a Profiler trace.

support.microsoft.com/.../912281

Tim

Was this reply helpful? Yes No
Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

I agree with Tim Foster reply.

Was this reply helpful? Yes No