"ORA-14450: attempt to access an existing transactional temp table" in a composite trigger

Question

I have a table that can contain many records for one account: different amounts.

ACCOUNTID | AMOUNT
id1       | 1
id1       | 2
id2       | 3
id2       | 4

Each time a record in this table is inserted / updated / deleted, we need to estimate the total amount to know whether we need to fire the event or not (by inserting data into another table). The amount is calculated based on the sum of the records (at the expense) present in this table.

The calculation of the sum should use the new values ​​of the records, but to check some conditions we will need the old values ​​(for example, the old value was X - the new value is Y: if [X <= threshold and Y> threshold] then trigger the event by inserting the record into another table )

So, in order to compute and trigger the event, we created a trigger in this table. Something like that:

CREATE OR REPLACE TRIGGER <trigger_name>
  AFTER INSERT OR UPDATE OR DELETE OF MOUNT ON <table_name>
  FOR EACH ROW
  DECLARE
BEGIN
  1. SELECT SUM(AMOUNT) INTO varSumAmounts FROM <table_name> WHERE accountid = :NEW.accountid; 
  2. varAmount :=  stored_procedure(varSumAmounts);
END <trigger_name>;

The problem is that operator 1. throws the following error: "ORA-04091: the table is mutating, the trigger / function may not see it."

We tried the following, but without success (same exception / error), to select all records whose rowId is different from the current rowId:

(SELECT SUM(AMOUNT) 
 INTO varSumAmounts 
 FROM <table_name> 
 WHERE accountId = :NEW.accountid 
       AND rowid <> :NEW.rowid;)

to calculate the sum as the sum of the sums of all the rows next to the current row + the sum of the current row (which we have in the context of the trigger).

We looked for other solutions, and we found some, but I don’t know which one is better, and what is the disadvantage for each of them (although they are somehow similar)

• Use complex trigger

• http://www.oracle-base.com/articles/9i/mutating-table-exceptions.php

• http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

"table is mutating" 1 2, .

, : OLD : NEW . :

CREATE OR REPLACE TRIGGER trigger-name
FOR trigger-action ON table-name
COMPOUND TRIGGER
-------------------
BEFORE STATEMENT IS
BEGIN
-- Delete data from global temporary table (GTT) for which source is this trigger
-- (we use same global temporary tables for multiple triggers).
END BEFORE STATEMENT;
-------------------
AFTER EACH ROW IS
BEGIN
-- Here we have access to :OLD and :NEW objects.
-- :NEW and :OLD objects are defined only inside ROW STATEMENTS.
-- Save relevant data regarding :NEW and :OLD into GTT table to use it later.
END AFTER EACH ROW;
--------------------
AFTER STATEMENT IS
BEGIN
-- In this block DML operations can be made on table-name(the same table on which 
--the trigger is created) safely.
-- Table is mutating error will no longer appear because this block is not for EACH ROW specific.
-- But we can't access :OLD and :NEW objects. This is the reason why in 'AFTER EACH ROW' we saved them in GTT.
-- Because previously we saved :OLD and :NEW data, now we can continue with our business logic.
-- if (oldAmount<=threshold && newAmount>threshold) then 
--    trigger event by inserting record into another table
END AFTER STATEMENT;
END trigger-name;
/

"ON COMMIT DELETE ROWS", , , . , : "ORA-14450: ".

, , oracle , : " (GTT) Distributed XA . ...

, , XA- . - XA-, . ...

, , XA , , 5344322. "

, XA DML , ( ). - ( ), . ?

, : "ORA-04091: , / ", : "ORA-14450: temp ??

Answer 1

, :

SQL> create global temporary table t (x int) on commit delete rows
  2  /

SQL> insert into t values(1)
  2  /

SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4  insert into t values(1);
  5  commit;
  6  end;
  7  /
declare
*
error in line 1:
ORA-14450: attempt to access a transactional temp table already in use 
ORA-06512: error in line 4 

Answer 2

DELETE FROM <temp-table-name> BEFORE STATEMENT AFTER STATEMENT, , GTT ON COMMIT PRESERVE ROWS ON COMMIT DELETE ROWS.

RECORD/TABLE. BEFORE STATEMENT BEFORE STATEMENT.

- :

CREATE OR REPLACE TRIGGER TRIGGER-NAME
FOR TRIGGER-action ON TABLE-NAME
COMPOUND TRIGGER

TYPE GTT_RECORD_TYPE IS RECORD (ID NUMBER, price NUMBER, affected_row ROWID);
TYPE GTT_TABLE_TYPE IS TABLE OF GTT_RECORD_TYPE;
GTT_TABLE GTT_TABLE_TYPE;

-------------------
BEFORE STATEMENT IS
BEGIN
    GTT_TABLE := GTT_TABLE_TYPE(); -- init the table variable   
END BEFORE STATEMENT;
-------------------
AFTER EACH ROW IS
BEGIN
    GTT_TABLE.EXTEND;
    GTT_TABLE(GTT_TABLE.LAST) := GTT_RECORD_TYPE(:OLD.ID, :OLD.PRICE, :OLD.ROWID);
END AFTER EACH ROW;
--------------------
AFTER STATEMENT IS
BEGIN
    FOR i IN GTT_TABLE.FIRST..GTT_TABLE.LAST LOOP
        -- do something with values
    END LOOP;
END AFTER STATEMENT;
END TRIGGER-NAME;
/