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

Thanks a lot. That was fast!

I am still a little confused. My application worked fine in 8.0.5, and I also used the same
techniques in 7.x

This if from the Oracle docs:
1.Execute all BEFORE statement triggers that apply to the statement.   2.Loop for each row affected by the SQL statement.

      a.Execute all BEFORE row triggers that apply to the statement.
      b.Lock and change row, and perform integrity constraint checking. (The
lock is not released   until the transaction is committed.)
      c.Execute all AFTER row triggers that apply to the statement.

It says here that the integrity constraints are checked before even AFTER row triggers are fired,
not just "after statement" triggers.

> 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.
Is there any way to accomplish that or do I have to re-write the logic pretending that I am
avoiding mutating table - i.e. using state package.

The reason why it's important to me is because one of the things that trigger is doing is calling
external procedure to send a message to another process to keep it in sync with the DB changes.

Thanks again for the prompt response

Sincerely,

Alla Gribov

> -----Original Message-----
> From: Thomas J. Kyte [mailto:tkyte_at_us.oracle.com]
> Sent: Wednesday, May 17, 2000 2:57 PM
> To: alla.gribov_at_metatel.com
> Subject: Re: Urgent - weird trigger probles in Oracle 8i Release 2
>
>
> In article <3922DAFB.276CEA99_at_metatel.com>,

"Thomas J. Kyte" wrote:

> 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.
Received on Wed May 17 2000 - 00:00:00 CEST