Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Maximum stored procedu...
Microsoft Dynamics GP (Archived)

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

(0) ShareShare
ReportReport
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

GO

If so, how did you fix the problem?

Thank you

*This post is locked for comments

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,003 User Group Leader on at
    RE: Maximum stored procedure, function, trigger, or view nesting level exceeded

    I agree with Tim Foster reply.

  • Suggested answer
    Tim Foster Profile Picture
    Tim Foster 8,515 on at
    RE: Maximum stored procedure, function, trigger, or view nesting level exceeded

    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

  • Suggested answer
    Mohamed El-Tohamy Profile Picture
    Mohamed El-Tohamy 305 on at
    RE: Maximum stored procedure, function, trigger, or view nesting level exceeded

    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. 

  • Emily Ann Profile Picture
    Emily Ann 620 on at
    Re: Maximum stored procedure, function, trigger, or view nesting level exceeded

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans