> 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.
>
> SQL:
> create table "log" (n integer, text varchar(30));
> create table master (pk integer, constraint pk_master primary key
> (pk));
> 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);
>
> Next:
> delete from master where pk=1;
>
> Results are:
>
> Firebird:
> 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
>
> PostgreSQL:
> 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
> possible.
> 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
> detail"
>
> What behavior is SQL stand. compliant ?

the spec often leaves implementation out of the equation. I'd say all event
sequences you have observed are quite valid.
Alan