Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: Mutating Tables Error -- Global Settings?

    I have several Oracle instances (all 8.1.7) with schemas that are (supposed to be) identical. I do not "own" these instances, rather they are owned by and supported by an outside vendor. Because of this, I am restricted from posting DDL.

    I inherited some custom triggers that our shop installed on tables in these Oracle instances. In at least two of the instances, the triggers work just fine. However, in three instances where the triggers were recently installed, when the triggers fire, we get the "table is mutating" error.

    I have done a search of this site and I have a (passable?) understanding of the what the "mutating tables" issue is. But I can't understand why the triggers will work in selected instances, but not in the newer ones.

    Here is some additional information:

    1. To focus in on one example: The table has two triggers. One trigger is an UPDATE/INSERT trigger AFTER EACH ROW. The other trigger is a DELETE trigger before EACH ROW.

    2. The table has cascading constraints to other tables that are referenced from within the trigger. These constraints are Not Deferrable, Valid and are enabled.

    3. The base table is owned by one schema; the triggers are owned by a separate schema.

    I have tried copying the DDL ffor the trigger from the instance that "works" to an instance that is not working. That did not improve anything.

    My questions:

    1. Is there a global setting for triggers that I can check/verify?

    2. How do I trace the statements when a trigger is fired (including the execution of the cascading constraints?

    I'm sorry if this is not very much information. I'm just scratching the surface of this and finding it very frustrating.

    Regards,

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    More than likely, there are constraints (RI) that are coming into play
    on the 3 NON working instances. Mutation occurs when you perform an
    action on a table and then reference that table in the trigger with another
    action ... You might find that in the trigger you are updating a row from
    a different table that has already been updated (or deleted) from a constraint. Look at the constraints with cascading deletes in this case ... check them against the databases that ARE working to see that the same
    constraints exists...

    HTH
    Gregg

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Okay, I think I found the problem. The code for the DELETE trigger on one table in the three non-working instances was the same as the code for the UPDATE/INSERT trigger. It references tables that are updated as a result of the DELETE cascade. When I snagged the code for the DELETE trigger from one of the working instances and updated it, the non-working instances stopped 'mutating'.

    Thanks for the help.

    Now I'm going to go get a margarita...

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Glad it worked out ... You might need 2 or 10 Margarita's !!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •