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

In article <3922EE2C.99EE6D_at_metatel.com>,   Alla Gribov <alla.gribov_at_metatel.com> wrote:
> 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.
> 3.Complete deferred integrity constraint checking.
> 4.Execute all AFTER statement 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.
>

No, it says "perform integrity constraint checking", not Perform ALL (hence it means "perform some of the integrity checks"). It can perform some checks that are atomic at the row level such as

o NULL vs NOT NULL
o check constraints

and these are done before the AFTER FOR EACH ROW.

Step 4 -- complete defered integrity constraint checking. Things that might have to be deferred in some cases to after the statement completes are done here, after the AFTER row triggers.

In Oracle8i release 8.1 all RI is done deferred, others are done at step 2.b. In Oracle8.0 release 8.0 and before SOME was, SOME was not --  depended on the circumstances. It is a bug actually that it is not deferred in 8.0 and before -- there are obscure cases where they did it at step 2b, resulting in error.

> > 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.
>

we don't need to pretend we are a mutating table -- i believe there is a very robust and better way to do this.

> 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

so 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 rollsback? 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.

>
> > -----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.
>
>

--
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.