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 -> PRAGMA AUTONOMOUS_TRANSACTION in a trigger

PRAGMA AUTONOMOUS_TRANSACTION in a trigger

From: Josh White <whitegoose_at_inorbit.com>
Date: 3 Apr 2005 20:56:38 -0700
Message-ID: <aafea0a8.0504031956.438cdf3@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. Received on Sun Apr 03 2005 - 22:56:38 CDT

Original text of this message

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