Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger

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