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.
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.
Have you hugged your backup today?