Well, I'm not sure about the spec precisely, but I found
out how Oracle seems to handle the delete/insert, etc
cases for at least on delete cascade.
It seems that they do the action (other than no action
obviously) immediately upon the statement, but still
defer the check for validity until commit time.
So, given:
create table a (a int unique);
create table b (b int references a(a) on delete cascade deferrable initially deferred);
insert into b values (3);
insert into a values (3);
select * from a; -- gives 1 row with 3
select * from b; -- gives 1 row with 3
delete from a;
select * from b; -- gives no rows
insert into a values (3);
commit;
select * from b; -- gives no rows
select * from a; -- gives 1 row with 3
This is related part 2 of how Hiroshi broke up the
issues with the deferred FK. If Oracle's right, then
we'd probably want to:Make all non-No Action triggers not deferrableAdd a check to the no action triggers to basically
doa select from pk table where <each key value is the same as the old values> and if we get a row we've inserted a row
withthis same value so the constraint will be satisfied.
The latter fails for MATCH PARTIAL, but that's a
completely different animal entirely...