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 deferrable
Add a check to the no action triggers to basically
do a 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 with this same value
so the constraint will be satisfied.
The latter fails for MATCH PARTIAL, but that's a
completely different animal entirely...
