Re: Urgent - weird trigger probles in Oracle 8i Release 2
Date: 2000/05/17
Message-ID: <8fuq17$pgv$1_at_nnrp1.deja.com>
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.
• 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.
the relevant part of this is:
This case reveals when Oracle effectively performs its constraint
checking: after the statement has been completely executed.
</quote>
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
Table created.
tkyte_at_ORA734.WORLD> create or replace trigger c_bi before insert on c
2 begin
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
tkyte_at_ORA734.WORLD> set serveroutput on
tkyte_at_ORA734.WORLD> insert into c
3 dbms_output.put_line( 'After insert ' );
4 end;
5 /
Trigger created.
2 select 1, 2 from dual
3 union all
4 select 2, 3 from dual
5 /
Before insert
Before insert 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