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.
