1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

oracle after instert trigger mutation problem

Discussion in 'SQL PL/SQL' started by ora_student, Nov 3, 2008.

  1. ora_student

    ora_student Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Basically i have two tables
    table 1 is
    Code (Text):

    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
    Code (Text):

    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

    Code (Text):

    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
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    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/cd/B10501_01/appdev.920/a96590/adg13trg.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

    Code (Text):
     
    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
    Code (Text):
     
    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

    Code (Text):
     
    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
    Code (Text):
     
     
    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
    Code (Text):
     
    sql>insert into transactions values(1,'abcd');
    1 row created.
     
    Great ! so the insert was successful. Let us query the results

    Code (Text):
     
    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
    :D
     
    Girish likes this.
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    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.
     
  4. ora_student

    ora_student Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    hi simply_dba! can i buy you some virtual coffee online? :D 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