It doesn't have to work like this (the way you describe above) --the EARLIEST a referential constraint might be able to be verified would be right after the "before for each row" and before the "after for each row" but there are many times when it cannot be verfied at that time.

The documentation actually says:

<quote concepts manual, v7.3, section in "the mechanisms of constraint checking">

• A multiple row INSERT statement, such as an INSERT statement
with nested SELECT statement, can insert rows that reference
one another. For example, the first row might have EMPNO as
200 and MGR as 300, while the second row might have EMPNO
as 300 and MGR as 200.

Each case reveals something about how and when Oracle performs
constraint checking.

The first case is easy to understand; a null is given for the foreign key value. Because nulls are allowed in foreign keys, this row is inserted successfully into the table.

The second case is more interesting. This case reveals when Oracle effectively performs its constraint checking: after the statement has been completely executed. To allow a row to be entered with the same values in the parent key and the foreign key, Oracle must first execute the statement (that is, insert the new row) and then check to see if any row in the table has an EMPNO that corresponds to the new row’s
MGR.
</quote>


the relevant part of this is:

This case reveals when Oracle effectively performs its constraint checking: after the statement has been completely executed.

I agree that in earlier releases, it would *sometimes* not fire the after, for each row trigger but that was never gauranteed or promised (quite the contrary actually). Here is an example showing it could fire the AFTER for each row trigger before checking constraints in earlier releases:

tkyte@ORA734.WORLD> create table c ( a int primary key,
x references c(a) );

Table created.

tkyte@ORA734.WORLD> create or replace trigger c_bi
before insert on c
2 begin
3 dbms_output.put_line( 'Before insert' );
4 end;
5 /
Trigger created.

tkyte@ORA734.WORLD> create or replace trigger c_bifer
before insert on c for each row
2 begin
3 dbms_output.put_line( 'Before insert FER '
|| :new.a || ' ' || :new.x );
4 end;
5 /
Trigger created.


tkyte@ORA734.WORLD> create or replace trigger c_aifer
after insert on c for each row
2 begin
3 dbms_output.put_line( 'Before after FER ' ||
:new.a || ' ' || :new.x );
4 end;
5 /
Trigger created.

tkyte@ORA734.WORLD> create or replace trigger c_ai
after insert on c
2 begin
3 dbms_output.put_line( 'After insert ' );
4 end;
5 /
Trigger created.

tkyte@ORA734.WORLD> set serveroutput on
tkyte@ORA734.WORLD> insert into c
2 select 1, 2 from dual
3 union all
4 select 2, 3 from dual
5 /
Before insert
Before insert FER 1 2
Before after FER 1 2
Before insert FER 2 3
Before after FER 2 3
insert into c
*
ERROR at line 1:
ORA-02291: integrity constraint (TKYTE.SYS_C00382) violated - parent key not found

See - it fired the AFTER FOR EACH ROW trigger 2 times before checking the consraint -- and that was release 7.3.


Back to the root problem -- your extproc. That is not transactional (the maintaining of the external data source). You should be aware that just because something makes it past the AFTER FOR EACH ROW trigger does not mean it'll be in the table!!!! Regardless of trigger firing order/RI or anything.

What about:

insert into T select * from really_big_table;

and you run out of space in the middle? or the server crashes? or the end user rolls back? half of the rows will make it out, half will not. the half that made it out will get rolled back and won't exist -- you have a mess.

Here is what I would do in your case.

1) my after, for each row trigger would queue a job in dbms_job to be executed right after I commit and the changes are permanent.

2) this job it queues would simply be a call to the procedure its calling in real time right now.

3) set up the job queues to check for jobs to run every 15 seconds or so.

that way -- its transactional -- if I commit - the extproc gets called. If I get rolled back (for WHATEVER reason and there are dozens that could affect you -- regardless of RI or not), then the jobs themselves will get removed from the queue. The rows will be propagated in the background --probably providing a better experience for the end user.

how does that sound? I use dbms_job for lots of stuff to make it transactional (like sending emails and such). I queue the request to perform the operation -- I do not perform the operation I cannot rollback. Very safe, very efficient, much more robust.