SQL Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

David Meego - Click for blog homepageToday, I resolved a very interesting case. We had a customer who had just upgraded from an early build of Microsoft Dynamics GP 10.0 to Microsoft Dynamics GP 2010 (11.0) and after the upgrade they were unable to save from the Customer Maintenance window.

Every time they would try to add a new record or update an existing Customer Master record, the system throw an unhandled exception error with the following details:

Unhandled database exception:
A save operation on table 'RM_Customer_MSTR' (45).
[Microsoft][SQL Server Native Client 10.0][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
EXCEPTION_CLASS_DB
DB_ERR_SQL_OBJ_NOT_FOUND

Capturing a DEXSQL.LOG for the problem showed the call to the zDP_RM00101SI stored procedure to insert the record followed by the reporting of the error. As the stored procedure failed, Dexterity tries again with an actual SQL INSERT statement which also failed with the same error message.

/* Date: 05/02/2012 Time: 15:38:48
SQLSTATE:(37000) Native Err:(217) stmt(456758368):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).*/
/*

There is a Knowledge Base (KB) article which discusses this error:

The article explains how the miscellaneous Database Property option for Recursive Triggers Enable should be set to false. This will prevent a trigger on a table from triggering itself again when it makes a change to table it is registered against. In our case this setting was already correctly set to false.

As this problem does not seem to affect most Dynamics sites, I felt that it must be related to some sort of customisation. As the error was being generated at the SQL level, the customisation would need to be at the SQL level....I suspected a custom trigger on the RM_Customer_MSTR (RM00101) table.

So, I asked the partner to check for triggers on the RM00101 table, and try disabling them to see if that allows the record to be saved.

On a standard GP 2010 system there is a zDT_RM00101U trigger. This is a new trigger (added for v10.0 SP2 and GP2010) which writes a UTC time stamp to the DEX_ROW_TS column of the table every time the table is updated. The DEX_ROW_TS field is used for synchronization with external systems, such as Dynamics CRM. Also see Understanding how Microsoft Dynamics GP works with Microsoft SQL Server.

When the partner checked the triggers on the RM00101 table, they found a custom trigger. Below is the code for that trigger:

Original SQL Trigger on RM00101 table

ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]
after insert, update as

update RM00102
set SHIPMTHD = RM00101.SHIPMTHD , TAXSCHID = RM00101.TAXSCHID
from RM00101
where RM00101.CUSTNMBR = RM00102.CUSTNMBR
and RM00101.CUSTNMBR in
(select CUSTNMBR from inserted)

The code updates the Shipping Method and Tax Schedule ID from the RM_Customer_MSTR (RM00101) table to the RM_Customer_MSTR_ADDR (RM00102) table for the saved customer. Once the trigger was disabled, the error went away.

NOTE: The scripts use the ALTER TRIGGER command as we were editing an existing trigger.

Why would this trigger which worked previously for the customer fail after the upgrade?


The answer becomes clearer when you check the RM_Customer_MSTR_ADDR (RM00102) table for triggers.

The RM00102 table also has zDT triggers for the Create, Delete and Update events. Reading the trigger scripts you can see that they also write back to the RM00101 table to update the time stamp in the DEX_ROW_TS column.

Can you see what was happening yet? .... Let me break it down:

  1. User Saves data from Microsoft Dynamics GP application.
     
  2. Data written to RM00101 table.
  3. Trigger zDT_RM00101U on RM00101 table executes and updates time stamp.
  4. Custom Trigger executes and updates RM00102 table.
  5. Data written to RM00102 table.
  6. Trigger zDT_RM00102U on RM00102 table executes and writes back to RM00101 table.
  7. Go back to step 2, and repeat until nesting level error generated.
     
  8. When error generated, all changes captured in the transaction are rolled back.
  9. Unhandled Exception reported back at the application.

So now we understand what is happening; How can we modify the custom trigger code to stop it causing an infinite loop?


My first thought was that we could change the custom trigger so that it would not update records that did not need to be changed.

So we rewrote the trigger to use joins and added statements into the where clause so that the update would only only change a record if the Shipping Method or Tax Schedule ID were not the desired final values and the Shipping Method and Tax Schedule ID from the Customer Master table were not blank.

First Revision of SQL Trigger on RM00101 table

ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]
after insert, update as

update a set SHIPMTHD = c.SHIPMTHD, TAXSCHID = c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD or a.TAXSCHID <> c.TAXSCHID)
and (c.SHIPMTHD <> '' and c.TAXSCHID <> '')

This code does work correctly by itself, but it did not prevent the nesting level exceeded error. The problem was that the update statement itself causes the triggers to fire even if the update does not actually change any records. So while the inserted data set returned to the trigger was empty, the update event still happens.

So we needed a way to prevent the update statement from running when it was not needed.

The second revision (below) included an if exists statement with a select query to check if there were any records that needed updating and so only execute the update statement when it is really needed.

Second Revision of SQL Trigger on RM00101 table

ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]
after insert, update as

if exists(
select a.CUSTNMBR, a.ADRSCODE, a.SHIPMTHD, a.TAXSCHID, c.CUSTNMBR, c.ADRSCODE, c.SHIPMTHD, c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD or a.TAXSCHID <> c.TAXSCHID)
and (c.SHIPMTHD <> '' and c.TAXSCHID <> '')
)

begin
update a set SHIPMTHD = c.SHIPMTHD, TAXSCHID = c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD or a.TAXSCHID <> c.TAXSCHID)
and (c.SHIPMTHD <> '' and c.TAXSCHID <> '')
end

This version of the code fixed the problem by breaking the infinite loop. 

However, while testing the code we noticed that if you entered only one of the Shipping Method or Tax Schedule ID fields, the code did not roll down that one field change to the Address (RM00102) table.

So the last change was to separate the code into two sections, one to handle the Shipping Method and one to handle the Tax Schedule ID.

Final Revision of SQL Trigger on RM00101 table

ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]
after insert, update as

if exists(
select a.CUSTNMBR, a.ADRSCODE, a.SHIPMTHD, c.CUSTNMBR, c.ADRSCODE, c.SHIPMTHD
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD)
and (c.SHIPMTHD <> '')
)

begin
update a set SHIPMTHD = c.SHIPMTHD
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD)
and (c.SHIPMTHD <> '')
end

if exists(
select a.CUSTNMBR, a.ADRSCODE, a.TAXSCHID, c.CUSTNMBR, c.ADRSCODE, c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.TAXSCHID <> c.TAXSCHID)
and (c.TAXSCHID <> '')
)

begin
update a set TAXSCHID = c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.TAXSCHID <> c.TAXSCHID)
and (c.TAXSCHID <> '')
end

With this final code, we had fixed the error and made the code behave much better than the original design.

Hope you enjoyed reading post, I know it was "fun" to solve the problem.

David

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)