"ORA-14450: attempt to access a transactional temp table already in use" in a compound trigger

Question

I have a table which can hold many records for one account: different amounts.

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

Every time a record in this table is inserted/updated/deleted we need to evaluate an overall amount in order to know if we should trigger or not an event (by inserting data into another table). The amount is computed based on the sum of records (per account) present in this table.

The computation of the amount should use new values of the records, but we need also old values in order to check some conditions (e.g. old value was X - new value is Y: if [X<=threshold and Y>threshold] then trigger event by inserting a record into another table).

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

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 issue is that statement 1. throws the following error: 'ORA-04091: table is mutating, trigger/function may not see it'.

We tried the following but without success (same exception/error) to select all records which have rowId different than current rowId:

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

in order to compute the amount as the sum of amounts of all rows beside current row + the amount of current row (which we have in the context of the trigger).

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

1. Use compound trigger

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

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

To avoid 'table is mutating' error based on solutions 1&2, I used a combination of compound triggers with global temporary tables.

Now we have a compound trigger which uses some global temporary tables to store relevant data from :OLD and :NEW pseudo records. Basically we do the next things:

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;
/

The global temporary tables used are created with option 'ON COMMIT DELETE ROWS', this way I make sure that data from this table will be cleaned at the end of the transaction. Yet, this error occurred: 'ORA-14450: attempt to access a transactional temp table already in use'.

The problem is that the application uses distributed transactions and in oracle documentation is mentioned that: "A variety of internal errors can be reported when using Global Temporary Tables (GTTs) in conjunction with Distributed or XA transactions. ...

Temporary tables are not supported in any distributed, and therefore XA, coordinated transaction. The safest option is to not use temporary tables within distributed or XA transactions as their use in this context is not officially supported. ...

A global temporary table can be safely used if there is only single branch transaction at the database using it, but if there are loopback database links or XA transactions involving multiple branches, then problems can occur including block corruption as per Bug 5344322. "

It's worth mentioning that I can't avoid XA transactions or making DML on same table which is the subject of the trigger (fixing the data model is not a feasible solution). I've tried using instead of the global temporary table a trigger variable - a collection (table of objects) but I am not sure regarding this approach. Is it safe regarding distributed transactions?

Which other solutions will be suitable in this case to fix either initial issue: 'ORA-04091: table name is mutating, trigger/function may not see it', or the second one: 'ORA-14450: attempt to access a transactional temp table already in use'?

Answer 1

You should carefuly check that you code doesn't use autonomous transactions to access temporary table data:

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