6 Replies Latest reply: Jun 7, 2011 9:19 AM by NoLongerActive RSS

    How to overcome mutating trigger error

    Jame
      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
          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
            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
              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
                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
                  Thanks guys

                  Got lot of left & right.....
                  • 6. Re: How to overcome mutating trigger error
                    NoLongerActive
                    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