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
You are welcome to log a bug. As a practical matter, I don't think it
will be addressed for a long time. Another bug which could be logged
would be to make the size of the trigger stack user-configurable. That
bug would be easier to address.
In the near term, the only workaround I can think of would be to build
your own version of Derby, setting DB2_MAX_TRIGGER_RECURSION to a higher
number than 16 in org/apache/derby/shared/common/reference/Limits.java
Hope this helps,
-Rick
On 8/18/24 7:26 PM, Emily Gouge wrote:
Hi,
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'm a bit confused because I don't think these are obviously nested.
Any thoughts or suggestions on resolving this?
Derby Version: 10.17.1.0
Below is a simple set of SQL statements that reproduces the issue I am
having.
Thanks,
Emily
create schema test;
--single root table
create table test.root(id integer primary key);
--child tables with foreign keys
create table test.child1(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child2(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child3(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child4(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child5(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child6(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child7(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child8(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child9(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child10(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child11(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child12(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child13(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child14(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child15(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child16(keyid integer primary key, id integer
references test.root(id) on delete set null);
create table test.child17(keyid integer primary key, id integer
references test.root(id) on delete set null);
-- logging table and triggers for logging
create table test.log(id integer);
create trigger trg_child1 after update on test.child1 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child2 after update on test.child2 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child3 after update on test.child3 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child4 after update on test.child4 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child5 after update on test.child5 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child6 after update on test.child6 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child7 after update on test.child7 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child8 after update on test.child8 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child9 after update on test.child9 REFERENCING old
as old for each row insert into test.log values(old.keyid);
create trigger trg_child10 after update on test.child10 REFERENCING
old as old for each row insert into test.log values(old.keyid);
create trigger trg_child11 after update on test.child11 REFERENCING
old as old for each row insert into test.log values(old.keyid);
create trigger trg_child12 after update on test.child12 REFERENCING
old as old for each row insert into test.log values(old.keyid);
create trigger trg_child13 after update on test.child13 REFERENCING
old as old for each row insert into test.log values(old.keyid);
create trigger trg_child14 after update on test.child14 REFERENCING
old as old for each row insert into test.log values(old.keyid);
create trigger trg_child15 after update on test.child15 REFERENCING
old as old for each row insert into test.log values(old.keyid);
create trigger trg_child16 after update on test.child16 REFERENCING
old as old for each row insert into test.log values(old.keyid);
create trigger trg_child17 after update on test.child17 REFERENCING
old as old for each row insert into test.log values(old.keyid);
-- no data is needed to create the problem
delete from test.root;
--results in: ERROR 54038: Maximum depth of nested triggers was exceeded.
