oracle after instert trigger mutation problem

[ora_student]

Basically i have two tables
table 1 is

CREATE TABLE transactions_serial (serial INTEGER)

table 2 is transactions table with xtest_field which is a varchar and a did which is an integer and primary key

there is a third table customer which also has did and drevisionid. now i am trying to create an after insert trigger on transactions so that every time a new row is inserted in transactions the latest "count" of xtest_field with drevisionid = 1, from transactions is inserted in transactions_serial

here is my code for the trigger

CREATE OR REPLACE TRIGGER transactions_after_insert
   AFTER INSERT
   ON transactions
   FOR EACH ROW
DECLARE
   serial   INTEGER;
BEGIN
   SELECT   COUNT (b.xtest_field)
       INTO serial
       FROM customer a, transactions b
      WHERE a.did = b.did
        AND a.drevisionid = 1
        AND b.xtest_field = :NEW.xtest_field
   GROUP BY b.xtest_field;
 
   INSERT INTO transactions_serial
               (serial_no
               )
        VALUES (serial
               );
END;

the trigger is created succesfully but when i try to insert into transactions it gives me a table is mutating error

ORA-04091: table TRANSACTIONS is mutating, trigger/function may not see it
ORA-06512: at "TRANSACTIONS_AFTER_INSERT", line 4
ORA-04088: error during execution of trigger 'TRANSACTIONS_AFTER_INSERT'

any idea how to bypass this? thanks in advance to everyone for their time

[simply_dba]

Hmmm.... A typical Mutating table error.

Well you cannot select from a table which you are inserting, all from the same row level trigger.
For more information on mutating table error and how and why causes this
http://download-west.oracle.com/docs...g13trg.htm#786

To workaround this issue, we need to capture the new xtest_field in to pl/sql table through row level trigger and then process the counts in a statement level trigger

Here it is

Create a package to maintain the state.We will declare two arrays -- one will hold the new xtest_fields and another will be empty to reset the array

sql>create or replace package state_pkg 
  2      as 
  3              type fldArray is table of varchar2(1000) index by binary_integer; 
  4     
  5              newRows fldArray; 
  6              empty   fldArray; 
  7      end; 
  8  /
Package created.

Now, we need to create a before insert tigger to maintain a consistent state before we begin processing row level trigger.This is mandatory, because during a multi insert operation, row level trigger may fire, but the statement level may not, if the multi insert statement fails, say, on the second row.In this case, the row level trigger will fire two times but the statement level will not, resulting in two erroneous xtest_fields in the array.Therefore, before the insert, we reset

sql>create or replace trigger transactions_bi 
  2      before insert or update on transactions
  3      begin 
  4              state_pkg.newRows := state_pkg.empty; 
  5      end; 
  6      / 
Trigger created.

Now, we start capturing the new xtest_fields in the array through a row level trigger

sql>create or replace trigger TRANSACTIONS_AFTER
  2      after insert or update of xtest_field on transactions for each row 
  3      begin 
  4         state_pkg.newRows( state_pkg.newRows.count+1):= :NEW.xtest_field;         
  5      end; 
  6      / 
Trigger created.

Now the following trigger will do the actual work. But in this modified case, to process the count, we simply loop over the collected xtest_fields from the array, do the count and insert them in to the transactions_serial table

 
 sql>create or replace trigger TRANSACTIONS_AFTER_INSERT
  2     after insert or update of xtest_field
  3     on transactions
  4  
  5  DECLARE
  6     serialno   INTEGER;
  7  BEGIN
  8     for i in 1 .. state_pkg.newRows.count loop 
  9  
 10     SELECT   COUNT (b.xtest_field)
 11         INTO serialno
 12         FROM customer a, transactions b
 13        WHERE a.did = b.did
 14          AND a.devidionid = 1
 15          AND b.xtest_field = state_pkg.newRows(i)
 16     GROUP BY b.xtest_field;
 17  
 18  
 19     INSERT INTO transactions_serial
 20                 (serial
 21                 )
 22          VALUES (serialno
 23                 );
 24  end loop;
 25  END;
 26  /
Trigger created.

So we are quite finished with the creations, now let us test with an insert

sql>insert into transactions values(1,'abcd');
1 row created.

Great ! so the insert was successful. Let us query the results

sql>select * from customer;
       DID DEVIDIONID NAME
---------- ---------- ---------------
         1          1 abcd
         1          1 abcd
         1          1 abcd
         1          1 abcd
         1          1 abcd
         2          2 efgh
6 rows selected.
 sql>select * from transactions;
       DID XTEST_FIELD
---------- -----------------------------------------------------------------------------------------
         1 abcd
1 row selected.
 sql>select * from transactions_serial;
 
    SERIAL
----------
         5
1 row selected.

Cheers

[tyro]

an excellent solution simply_dba, i haven't tried it out but i will try and see. Mutation problem may sometimes seem really annoying as it is a lot simpler to just think of writing an after-insert trigger for validations on the same table.

ora_student, you can also consider using a view and an instead of trigger to update the view. May make for a simpler solution.

[ora_student]

hi simply_dba! can i buy you some virtual coffee online? You absolutely made my day, i got the serial count running absolutely. Also as you can make out i am a beginner in oracle. i was able to follow your examples and use arrays in package to make several calculations in some of my other assignments. Thank you brother, drop me a PM if i can be of any help for u

and tyro, thanks 4 the suggestion, i looked up INSTEAD OF trigger but i have already implemented simply_dba's solution