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