Article > Order of triggers and foreign key cascades in Firebird
Description :: How do before and after triggers, with their position number, interact with cascade deletes and cascade set nulls?
Tested in Firebird 2.1. Notes and interpretations and algorithm first, then essential table definitions, test script, results, and then other scripts and secondary tests.

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
Continued at top
Owned by Unordained - Created on 11/18/2011 - Last edited on 12/29/2011
Sort 21 items by: Ranking - Owner - Last update - Type - Title