Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger

whitegoose_at_inorbit.com (Josh White) wrote in message news:<aafea0a8.0504031956.438cdf3_at_posting.google.com>...
> I am heading down the track of using PRAGMA AUTONOMOUS TRANSACTION in
> a BEFORE INSERT OR UPDATE trigger to avoid 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.
>
> 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)?
>
> 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?
>
> 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.
>
> Josh.

In case anyone is interested, I did end up using PRAGMA AUTONOMOUS_TRANSACTION to get around the mutating table problems... I defined the PRAGMA AUTONOMOUS_TRANSACTION in a stored procedure, and my trigger calls that stored procedure.

This way the stored procedure can select from the table the trigger is on independantly of the trigger, and I can pass a return value back to the trigger to determine whether the trigger needs to RAISE_APPLICATION_ERROR - If I RAISE_APPLICATION_ERROR in an AUTONOMOUS_TRANSACTION code block, the RAISE_APPLICATION_ERROR will not cause the triggering action to rollback. Received on Wed Apr 13 2005 - 07:29:59 CDT