[DERBY-7167] ERROR 54038: Maximum depth of nested triggers was exceeded

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.17.1.0
• Component/s: SQL
• Environment: Windows 11 ; Java 21.0.2
• Urgency: Normal
• Issue & fix info: Newcomer, Repro attached
• Bug behavior facts: Seen in production, Wrong query result
• Assignee: Unassigned
• Reporter: Emily Gouge
• Created: 05/Sep/24 16:28
• Updated: 05/Sep/24 17:18

Description

I have a single table which is referenced by more than 16 other tables. 
These 16+ children tables all have "on delete set null" foreign key 
constraints and update triggers which log changes to a change log table. 
When I try to delete from the root table I am running into a "ERROR 
54038: Maximum depth of nested triggers was exceeded".

 

I've attached a script that reproduces the issue.

 

Response from posting the question on the Mailing List:

https://lists.apache.org/thread/s4h5lvp9frmt48vwt4fvpn2wgczry3pr

At first glance, it does not appear that there is any recursion involved 
here. However, as implemented, preceding triggers are still active when 
later triggers begin executing. This causes the active trigger stack to 
exceed its limited size. I am not an expert in this part of the code and 
I don't know how well Derby's trigger stack models the behavior of the 
SQL Standard's understanding of the trigger stack. Some of these 
complexities are discussed at 
_https://issues.apache.org/jira/browse/DERBY-1261_

 

Issue Links

• relates to: DERBY-1261 Two triggers on same table cause "ERROR 54038: Maximum depth of nested triggers was exceeded."