Subject Re: SQL spec. and order of delete operations
Author lacakus
I made some test on this.
I have created "master" and "detail" tables on both before and after
triggers, which writes into "log table".
"detail" table references "master" table with on delete cascade.

create table "log" (n integer, text varchar(30));
create table master (pk integer, constraint pk_master primary key
create table detail (fk integer, constraint fk_detail_master foreign
key (fk) references master(pk) on delete cascade);

insert into master VALUES(1);
insert into master VALUES(2);

insert into detail VALUES(1);
insert into detail VALUES(1);
insert into detail VALUES(2);
insert into detail VALUES(2);

delete from master where pk=1;

Results are:

1.Before delete master (deleting master row is present)
2.After delete master (deleting master row is gone)
3.Before delete detail 1
4.After delete detail 1
Before delete detail 2
After delete detail 2

1. Before master
2. Before detail 1,2 (deleting master row is gone)
3. After detail 1,2
4. After master

MSSQL 2000: (does not support before triggers)
1. After delete detail (deleting master row is gone)
2. After delete master

MySQL: Note: Currently, triggers are not activated by cascaded
foreign key actions. This limitation will be lifted as soon as
1. Before delete master
2. After delete master

Do you see diferences ?
1. MSSQL and PostgreSQL performs "detail" before "after master"
2. PostgreSQL performs "before detail" for all affected rows and
then "after detail" for all affected rows
Firebird performs per every row "before detail" and then "after

What behavior is SQL stand. compliant ?