Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger

Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger

From: <whitegoose_at_inorbit.com>
Date: 4 Apr 2005 00:47:35 -0700
Message-ID: <1112600855.736940.296630@o13g2000cwo.googlegroups.com>


Sybrand Bakker wrote:
> Comments embedded.
>
> On 3 Apr 2005 20:56:38 -0700, whitegoose_at_inorbit.com (Josh White)
> wrote:
>
> >I am heading down the track of using PRAGMA AUTONOMOUS TRANSACTION
in
> >a BEFORE INSERT OR UPDATE trigger to avoid mutating table errors.
>
> pragman autonomous transaction has *NOTHING* to do with mutating
table
> errors.
>
> The
> >trigger needs to, among other things, sum up the values of a certain
> >column of the table on which the trigger is sitting.
>
>
> If you need to sum up columns of the *current* record, you don't need
> to select/
>
> >
> >By declaring the trigger like:
> >--------------------------------------
> >CREATE OR REPLACE TRIGGER BldgGraffiti
> >BEFORE INSERT OR UPDATE
> >ON XBLDG_GR
> >REFERENCING NEW AS newRow OLD AS oldRow
> >FOR EACH ROW
> >
> >DECLARE
> > PRAGMA AUTONOMOUS_TRANSACTION;
> > Err_Num NUMBER;
> > Err_Desc VARCHAR2(500);
> >--------------------------------------
> >
> >Does this then allow the trigger to select from the table it is on
> >(XBLDG_GR)?
>
> Has nothing to do with it. Pragma autonomous transaction doesn't
> resolve this.
>
> >
> >Also, will declaring the trigger like this mean that I will be able
to
> >RAISE_APPLICATION_ERROR in the trigger with the intention of rolling
> >back anything the trigger has done AND the actual insert/updated
that
> >fired the trigger?
> >
>
> Again has nothing to do with it.
>
> >Thanks in advance - I cannot work this out myself because I don't
have
> >an Oracle database on my machine to test my code until next week -
and
> >I am new to Oracle.
>
> If you don't have the software, download it from otn.
> >
> >Josh.
>
> --
> Sybrand Bakker, Senior Oracle DBA

This was taken from http://techrepublic.com.com/5100-6313-5034684.html#



Autonomous transactions give you a way out. You can actually make your trigger an autonomous transaction. Remember that an autonomous transaction is an embedded block of code that executes from within a parent transaction, but it is treated by Oracle as a completely independent transaction.

This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred-that is to say that any changes are uncommitted and the autonomous transaction doesn't see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn't exist, for Oracle does not perceive that there is any choice between an unchanged table and an uncommitted but changed one.


I have read many articles that advocate the (careful) use of PRAGMA AUTONOMOUS_TRANSACTION to avoid mutating table errors in triggers. Mind you the article I've quoted here does point out that PRAGMA AUTONOMOUS_TRANSACTION can be used to hide design logic errors. Received on Mon Apr 04 2005 - 02:47:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US