Re: Urgent - weird trigger probles in Oracle 8i Release 2

In article <3922DAFB.276CEA99_at_metatel.com>,   Alla Gribov <alla.gribov_at_metatel.com> wrote:
> I just upgraded to the Oracle 8i Release 2 (running on Solaris) and I
 am
> expiriencing the most weird thing I've ever seen.
>
> I always believed that if there is an integrity constraints (like
> foreign key) and AFTER(!!!!) row trigger on a table, then the
 integrity
> constraint would be checked first and then the trigger would NOT be
> fired.
>
> And this is how it always worked for me before (or at least I thought
> so)
>
> Now, in Oracle 8.1.6., when I am trying to insert a row and it fails
> giving me ORA-02291 integrity constraint violated - parent key not
> found, the AFTER ROW trigger is being fired anyway.
> WHY???????????????????
>
> Please help me, it's really urgent.
>
> If this trigger is being fired anyway, how do I stop it from
> executing????? How do I know inside the trigger, that the statement
 will
> fail (or actually already failed)????
>
> Thanks a lot
>
> Alla Gribov
>
>

can you elaborate on why you believe you need to stop the trigger from firing since any and all work it performs will be rolled back as a side effect of the constraint violation -- its work will be undone in effect. If you really need the trigger to fire only after the constraint has been checked, the only correct method to achieve this in ALL releases of Oracle is to use an AFTER, not for each row, trigger. I can show you how to do that if need be (we can do everything in an AFTER trigger we can in an AFTER FOR EACH ROW if need be, including accessing the changed rows). Unless you are stuffing a message on a pipe or using utl_file (operations that do not rollback) -- the fact the trigger fires should not be relevant.

As to your question tho, it doesn't have to work like this -- 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_at_ORA734.WORLD> create table c ( a int primary key, x references c (a) );

Table created.

tkyte_at_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_at_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_at_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_at_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_at_ORA734.WORLD> set serveroutput on
tkyte_at_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.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.