Deduced algorithm (this is what you probably came here for)
set savepoint 1
try
with single row being modified
old = get current row()
new = old
for each before-trigger, order by position asc
new = trigger(old, new)
end for
write changes to row
for each after-trigger, order by position asc
trigger (old, new)
end for
for each referencing foreign key definition for which a target value has changed in the current row, order by [black box]
if updating and on update cascade
update every matching child row with new values (recursive)
old values
if updating and on update set null/default
update every matching child row with null/default (recursive)
if updating and on update restrict
if any matching child rows
throw exception 'foreign key references are present for the record'
if deleting and on delete cascade
delete every matching child row (recursive)
if deleting and on delete set null/default
update every matching child row with null/default (recursive)
if deleting and on delete restrict
if any matching child rows
throw exception 'foreign key references are present for the record'
end for
end with
catch any
rollback to savepoint 1
Interpretation
The parent is fully deleted before the child starts its cascaded deletion, which is why both before- and after- delete triggers on a child are unable to 'see' their (ex-)parent record; also, during cascade-updates (try modifying the ID of the parent row), the parent row will have its new primary key value by the time the before-update trigger runs on the child, where the foreign key still has its old value, but at least you have access to the new PK and can use it to find the parent record that way.
I have not attempted to determine how it decides whether fk_child_a or fk_child_b should be updated first; I suspect it's based on the order in which the foreign key constraints were created. (A larger script would be needed to know for sure.) I don't know of a mechanism for declaring the order in which the foreign keys should cascade. Is it using a constraint ID of some sort, or ordering by the actual user-chosen constraint name (which for automatically-named constraints will likely be the same order as an ID)?
Also, I haven't included the results of selecting from the tables being updated, while inside the trigger. As far as I know, until all before triggers have fired, the row you select should be as it was before any triggers started firing (would need to confirm, with position 0 and position 1 triggers making other trivial changes to the row). All after triggers should see the data as it was in the last before trigger (plus any default values?).
Another thing to test: add a second, 'unique not null' (nearly primary key) field to the parent table, with foreign keys referencing it; see in what order those foreign keys are updated relative to primary key ones: does the order in which constraints were built make a difference? If a child table has foreign keys to both, does it get updated twice? In that case, is the row sometimes in a visibly invalid state (visible to selects in other triggers) while other work is being done? Test performed, a table with two interesting foreign keys is updated in two separate passes.
Yet another test: how far along in the process are on-delete-restrict constraints enforced? You can't directly test it with the method here, as the constraint violation bubbles back up and rolls back the logs, but with a UDF (user-defined function) that writes to disk, I can confirm that all the previous log entries appear before it hits the foreign key constraint for which there are rows that aren't set to be deleted. It doesn't check up-front.
In a trigger, OLD always refers to the original row status, before any triggers were fired. NEW always refers to the output of the previous trigger, if any, or to the original values. Therefore, if you want your insert/update triggers to stack properly, you should be looking at NEW.* to avoid undoing/ignoring the work of a previous trigger.
Base tables
create table fk_parent ( id integer not null primary key ); create table fk_child_a ( id integer not null primary key, fk_parent integer references fk_parent (id) on update cascade on delete cascade ); create table fk_child_b ( id integer not null primary key, fk_parent integer references fk_parent (id) on update cascade on delete set null ); create table fk_grandchild_c ( id integer not null primary key, fk_child_a integer references fk_child_a (id) on update cascade on delete cascade ); create table fk_child_d ( id integer not null primary key, fk_parent1 integer references fk_parent (id) on update cascade on delete set null, fk_parent2 integer references fk_parent (id) on update cascade on delete set null ); create table fk_child_e ( id integer not null primary key, fk_parent integer references fk_parent (id) on update cascade ); create table event_log ( id integer not null primary key, msg varchar(1000) );
Test
delete from fk_parent; delete from fk_child_a; delete from fk_child_b; delete from fk_child_d; -- delete from fk_child_e; delete from fk_grandchild_c; insert into fk_parent (id) values (1); insert into fk_child_a (id, fk_parent) values (1, 1); insert into fk_child_a (id, fk_parent) values (2, 1); insert into fk_child_b (id, fk_parent) values (3, 1); insert into fk_child_b (id, fk_parent) values (4, 1); insert into fk_grandchild_c (id, fk_child_a) values (5, 1); insert into fk_child_d (id, fk_parent1, fk_parent2) values (6, 1, 1); -- insert into fk_child_e (id, fk_parent) values (7, 1); delete from event_log; delete from fk_parent where id = 1; select * from event_log order by id asc;
Results
log_parent_before_0 deleting log_parent_before_1 deleting log_parent_after_0 deleting log_parent_after_1 deleting log_child_a_before_0 deleting log_child_a_before_1 deleting log_child_a_after_0 deleting log_child_a_after_1 deleting log_grandchild_c_before_0 deleting log_grandchild_c_before_1 deleting log_grandchild_c_after_0 deleting log_grandchild_c_after_1 deleting log_child_a_before_0 deleting log_child_a_before_1 deleting log_child_a_after_0 deleting log_child_a_after_1 deleting log_child_b_before_0 updating log_child_b_before_1 updating log_child_b_after_0 updating log_child_b_after_1 updating log_child_b_before_0 updating log_child_b_before_1 updating log_child_b_after_0 updating log_child_b_after_1 updating log_child_d_before_0 updating log_child_d_before_1 updating log_child_d_after_0 updating log_child_d_after_1 updating log_child_d_before_0 updating log_child_d_before_1 updating log_child_d_after_0 updating log_child_d_after_1 updating -- if 'e' was used, 'e' blows up here (according to write-to-disk logs)
Other
create generator event_log_id;
create procedure log_event (msg varchar(1000)) as
begin
insert into event_log (id, msg) values (gen_id(event_log_id, 1), :msg);
end
create trigger log_parent_before_0 for fk_parent
before insert or update or delete position 0
as
begin
execute procedure log_event ('log_parent_before_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_parent_before_1 for fk_parent
before insert or update or delete position 1
as
begin
execute procedure log_event ('log_parent_before_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_parent_after_0 for fk_parent
after insert or update or delete position 0
as
begin
execute procedure log_event ('log_parent_after_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_parent_after_1 for fk_parent
after insert or update or delete position 1
as
begin
execute procedure log_event ('log_parent_after_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_a_before_0 for fk_child_a
before insert or update or delete position 0
as
begin
execute procedure log_event ('log_child_a_before_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_a_before_1 for fk_child_a
before insert or update or delete position 1
as
begin
execute procedure log_event ('log_child_a_before_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_a_after_0 for fk_child_a
after insert or update or delete position 0
as
begin
execute procedure log_event ('log_child_a_after_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_a_after_1 for fk_child_a
after insert or update or delete position 1
as
begin
execute procedure log_event ('log_child_a_after_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_b_before_0 for fk_child_b
before insert or update or delete position 0
as
begin
execute procedure log_event ('log_child_b_before_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_b_before_1 for fk_child_b
before insert or update or delete position 1
as
begin
execute procedure log_event ('log_child_b_before_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_b_after_0 for fk_child_b
after insert or update or delete position 0
as
begin
execute procedure log_event ('log_child_b_after_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_b_after_1 for fk_child_b
after insert or update or delete position 1
as
begin
execute procedure log_event ('log_child_b_after_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_grandchild_c_before_0 for fk_grandchild_c
before insert or update or delete position 0
as
begin
execute procedure log_event ('log_grandchild_c_before_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_grandchild_c_before_1 for fk_grandchild_c
before insert or update or delete position 1
as
begin
execute procedure log_event ('log_grandchild_c_before_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_grandchild_c_after_0 for fk_grandchild_c
after insert or update or delete position 0
as
begin
execute procedure log_event ('log_grandchild_c_after_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_grandchild_c_after_1 for fk_grandchild_c
after insert or update or delete position 1
as
begin
execute procedure log_event ('log_grandchild_c_after_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_d_before_0 for fk_child_d
before insert or update or delete position 0
as
begin
execute procedure log_event ('log_child_d_before_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_d_before_1 for fk_child_d
before insert or update or delete position 1
as
begin
execute procedure log_event ('log_child_d_before_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_d_after_0 for fk_child_d
after insert or update or delete position 0
as
begin
execute procedure log_event ('log_child_d_after_0 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
create trigger log_child_d_after_1 for fk_child_d
after insert or update or delete position 1
as
begin
execute procedure log_event ('log_child_d_after_1 ' || case when updating then 'updating' when deleting then 'deleting' when inserting then 'inserting' else '' end);
end
Another test, for NEW and OLD variables
create table child_f (
id integer not null primary key,
field1 varchar(4)
);
create or alter trigger log_child_f_0 for child_f before update position 0 as
declare variable selected_field1 varchar(4);
declare variable new_field1 varchar(4);
begin
new_field1 = new.field1;
select field1 from child_f where id = old.id into :selected_field1;
selected_field1 = coalesce(:selected_field1, 'NULL');
new.field1 = new.field1 || new.field1;
execute procedure log_event('log_child_f_0 old = ' || old.field1 || ', new was ' || :new_field1 || ', new = ' || new.field1 || ', sel = ' || :selected_field1);
end
create or alter trigger log_child_f_1 for child_f before update position 1 as
declare variable selected_field1 varchar(4);
declare variable new_field1 varchar(4);
begin
new_field1 = new.field1;
select field1 from child_f where id = old.id into :selected_field1;
selected_field1 = coalesce(:selected_field1, 'NULL');
new.field1 = new.field1 || new.field1;
execute procedure log_event('log_child_f_1 old = ' || old.field1 || ', new was ' || :new_field1 || ', new = ' || new.field1 || ', sel = ' || :selected_field1);
end
create or alter trigger log_child_f_2 for child_f after update position 0 as
declare variable selected_field1 varchar(4);
declare variable new_field1 varchar(4);
begin
new_field1 = new.field1;
select field1 from child_f where id = new.id into :selected_field1;
selected_field1 = coalesce(:selected_field1, 'NULL');
execute procedure log_event('log_child_f_2 old = ' || old.field1 || ', new = ' || new.field1 || ', sel = ' || :selected_field1);
end
insert into child_f (id, field1) values (1, 'a');
delete from event_log;
update child_f set id = 2 where id = 1;
update child_f set field1 = 'b' where id = 2;
select * from event_log order by id asc;
Results of second test
log_child_f_0 old = a, new was a, new = aa, sel = a log_child_f_1 old = a, new was aa, new = aaaa, sel = a log_child_f_2 old = a, new = aaaa, sel = aaaa log_child_f_0 old = aaaa, new was b, new = bb, sel = aaaa log_child_f_1 old = aaaa, new was bb, new = bbbb, sel = aaaa log_child_f_2 old = aaaa, new = bbbb, sel = bbbb
