"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)

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'?

What's the "business logic" done in your `AFTER STATEMENT` trigger? Is it feasible to implement it in another way that doesn't require temporary tables (like maybe using materialized views)? If not, is it feasible (even though a bit of a "hack") to use "real" tables instead of temporary tables (maybe with a recurring job that deletes rows that are no longer needed). — Brian Camire, Feb 28 '14 at 16:13
The data saved in after each row is processed and based on some conditions a record is inserted into another table which triggers other processes (via JMS, these processes some times being connected with third parties). I don't know if materialized views, with the hacking or real tables can be a solution, usually we try to keep it as simple as possible. — marius_work, Mar 03 '14 at 08:32

score 1 · Answer 1 · answered Feb 28 '14 at 14:14

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

Wernfried Domscheit · Answer 2 · 2014-02-28T15:40:32.020

In case you do a DELETE FROM <temp-table-name> in BEFORE STATEMENT and AFTER STATEMENT is should not matter if you GTT is defined with ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS.

In your trigger you can define a RECORD/TABLE variable. This variable you can initialize in BEFORE STATEMENT block and loop over it in BEFORE STATEMENT block.

Would be something like this:

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

I've mentioned that "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?" — marius_work, Feb 28 '14 at 15:45
For my first suggestion, I don't see any reason for a conflict but I am not sure. However, with the variable approach you should be on the safe side in any case. — Wernfried Domscheit, Feb 28 '14 at 15:57

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

2 Answers2