This discussion is archived
6 Replies Latest reply: Jun 7, 2011 7:19 AM by NoLongerActive RSS

How to overcome mutating trigger error

Jame Newbie
Currently Being Moderated
Hi Guys,

I am getting mutating error while executing the trigger. Let me explain you in detail.

I have master table called Order and it contains order_status column. Our requirement is when ever order status is changed to
'packed' then trigger body query should get execute and the result set should be populate into table from another schema.

While executing the below statement i am getting the mutating error.
UPDATE ORDERS SET ORDER_STATUS='ORDPACKED' WHERE INTERNAL_ORDER_ID ='150'

ERROR:
UPDATE ORDERS SET ORDER_STATUS='ORDPACKED' WHERE INTERNAL_ORDER_ID ='150'
*
ERROR at line 1:
ORA-04091: table L2000.ORDERS is mutating, trigger/function may not see it
ORA-06512: at "L2000.TRG_SPL_VAS_TRACK", line 16
ORA-06512: at "L2000.TRG_SPL_VAS_TRACK", line 88
ORA-04088: error during execution of trigger 'L2000.TRG_SPL_VAS_TRACK'

Just i am selecting the order table in the trigger body but not performing any update or delete then why i am getting this
mutating error(On assumption, mutating error will come whenever we try to modify the same column)

Please let me know if you more information on this regard.

Regards,
Jame
  • 1. Re: How to overcome mutating trigger error
    sb92075 Guru
    Currently Being Moderated
    Jame wrote:
    Hi Guys,

    I am getting mutating error while executing the trigger. Let me explain you in detail.

    I have master table called Order and it contains order_status column. Our requirement is when ever order status is changed to
    'packed' then trigger body query should get execute and the result set should be populate into table from another schema.

    While executing the below statement i am getting the mutating error.
    UPDATE ORDERS SET ORDER_STATUS='ORDPACKED' WHERE INTERNAL_ORDER_ID ='150'

    ERROR:
    UPDATE ORDERS SET ORDER_STATUS='ORDPACKED' WHERE INTERNAL_ORDER_ID ='150'
    *
    ERROR at line 1:
    ORA-04091: table L2000.ORDERS is mutating, trigger/function may not see it
    ORA-06512: at "L2000.TRG_SPL_VAS_TRACK", line 16
    ORA-06512: at "L2000.TRG_SPL_VAS_TRACK", line 88
    ORA-04088: error during execution of trigger 'L2000.TRG_SPL_VAS_TRACK'

    Just i am selecting the order table in the trigger body but not performing any update or delete then why i am getting this
    mutating error(On assumption, mutating error will come whenever we try to modify the same column)

    Please let me know if you more information on this regard.

    Regards,
    Jame
    do NOT do SQL against table upon which the trigger is based.
  • 2. Re: How to overcome mutating trigger error
    sybrand_b Guru
    Currently Being Moderated
    Let me explain you in detail.

    Sorry, but you didn't post the trigger code, so there aren't any details.
    What I see is much bla bla, boiling down to the assertion it doesn't work as expected.
    You also state you are selecting from the table you update.
    And you are still surprised you get the error?

    --------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: How to overcome mutating trigger error
    BluShadow Guru Moderator
    Currently Being Moderated
    Jame wrote:
    Just i am selecting the order table in the trigger body but not performing any update or delete then why i am getting this
    mutating error(On assumption, mutating error will come whenever we try to modify the same column)
    The clue is in the error.

    The table you are trying to select from in the trigger is changing as you do it. The reason that it's changing is because of the update statement that is firing the trigger. You can't query the very same table you are currently in the process of updating.

    Perhaps if you explained what you are trying to achieve then we can assist.

    {message:id=9360002}
  • 4. Re: How to overcome mutating trigger error
    6363 Guru
    Currently Being Moderated
    Jame wrote:

    Just i am selecting the order table in the trigger body but not performing any update or delete then why i am getting this
    mutating error(On assumption, mutating error will come whenever we try to modify the same column)
    Nice assumption, unfortunately not reality.

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17766/e2100.htm#sthref1923

    >
    ORA-04091: table string.string is mutating, trigger/function may not see it
    Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
    Action: Rewrite the trigger (or function) so it does not read that table.
    >

    The mutating table error is like the safety catch on a gun that makes it go click when you aim it at your toes and pull the trigger. You should not work around it, it is trying to tell you what you are trying to do is not reliable. Autonomous transactions are not a fix for this.
  • 5. Re: How to overcome mutating trigger error
    Jame Newbie
    Currently Being Moderated
    Thanks guys

    Got lot of left & right.....
  • 6. Re: How to overcome mutating trigger error
    NoLongerActive Pro
    Currently Being Moderated
    Hi,

    If you must get round the problem, then write it as two triggers.

    1 - Row Level trigger which stores the details of the things you need to update somewhere else (e.g. in a PL/SQL table)

    2 - Statement level trigger which loops through the records you stored and does the operation you require.

    HTH,

    Matt

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points