The Oracle mutating table error occurs in case a database trigger performs an update on a table that already has been updated.
An example of the Oracle Mutating Table Error (ORA-04091)
Here is an example showing how Hora’s Mutating Table Trigger Wizard can come in handy to solve the mutating table error. One situation in which this could happen is when an attempt is made to update an aggregate amount in a master table, based on DML operations to a detail table.
For example, if we were to add a column called AVERAGE_SALARY to the HR.EMPLOYEES table, based on the salaries in the HR.EMPLOYEES table…
Then, if we attempted to write a FOR EACH ROW DELETE trigger like the following one, an attempt to delete rows from the EMPLOYEES table would fail:
In essence, the message is saying that in the AFTER DELETE…FOR EACH ROW trigger, it’s difficult to ascertain what the new average salary for the department should be.
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.
Setting up the Mutating Table Trigger Wizard
Compound triggers are employed by Hora’s Mutating Table Trigger Wizard to make it easy to solve this problem for delete, updates, and inserts at the same time. The Wizard can be invoked from the context menu:
In the screenshot above, we’ve done very little to alter the default values presented by the wizard: the default trigger name EMPLOYEES_CM has been changed to UPDATE_AVERAGE_SALARY_CM, and the “Aggregate Function” and “Column to aggregate” fields have been filled in. EMPLOYEES has multiple Master Tables; so we need to be sure that the right one is selected (in this case, DEPARTMENTS), and the “Aggregate Column” field identifies the affected column in the master table.
By default, the trigger code handles updates and inserts, as well as deletes.
If we hit OK, and have SQL Preview turned on, the trigger code appears:
Examining the code and reviewing the result
The compound trigger consists of three parts: the declaration, the BEFORE EACH ROW section and the AFTER STATEMENT section.
Before each DML operation, the affected DEPARTMENT_ID is stored in the nested table “key_tab.”
After the individual rows in the EMPLOYEES table are updated, in the AFTER STATEMENT section, the EMPLOYEE rows for the changed departments are read into the variable “agg, ” which is then used to update the DEPARTMENTS table. This in effect does away with the mutating table situation because the code executes only after all the rows have been updated.
It is also possible to customize the code, for example, by adding a DBMS_OUTPUT.PUT_LINE like this:
When we try our delete statement again the rows are deleted, and the trigger’s message is displayed:
As you can see, the Wizard makes it a simple matter to set up the compound trigger syntax and provide the standard choices for dealing with mutating tables. All that’s left is filling in the details.
Since the release of KeepTool 11 in February of this year, we’ve brought out 4 minor releases—each of which adds significant functionality to Hora’s existing capabilities. You can find a summary of these in the Release Notes of KeepTool’s help file. Look for another edition of Keeping in Touch in the latter part of this year!
