I noticed today that delete cascade triggers are fired "after delete"
while I was convinced that they happen before delete.

Is this a bug?

I have before delete triggers on detail table that work if you delete a row
from detail table but if you delete a row from the master the detail can't
find the master row anymore. I think the master should not be deleted until
all details have been deleted. The trigger should be "before delete" not
after.

I tested Firebird 1.5 RC4 and Firebird 1.0 and they both behave the same.
I don't have Interbase so I can't test that but I bet is the same.

I _do_ consider it a bug and a serious one for that matter.

This script can be used to see the problem:

create table t1(f1 integer not null primary key,
f2 integer);
create table t2(f1 integer not null primary key,
f2 integer references t1(f1)
on delete cascade);
set term ``;
create trigger tr_t2_bd for t2
before delete
as
declare variable af1 integer;
begin
select f1 from t1
where f1=old.f2
into :af1;
insert into t1(f1,f2) values (10,:af1);
end``
set term ;``
commit;
insert into t1(f1,f2) values (1,0);
insert into t1(f1,f2) values (2,0);
insert into t2(f1,f2) values (1,1);
insert into t2(f1,f2) values (2,2);
commit;
delete from t1 where f1=2;
commit;

According to the trigger on table t2 we should have a row (10, 2) in t1
but when before delete executes, the row(2,0) from t1 is already
deleted from the master, so af1 becomes null and the row inserted in
t1 is (10,null).

Apparently this behaviour is here from the begining of time but still: is it
right?


Ciao,

Best regards,
Application Developer
Calin Iancu, Pirtea
S.C. SoftScape S.R.L.
pcalin@...