Using KeepTool’s Mutating Table Trigger Wizard


An example of the 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…

mutating-trigger-1

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:

mutating-trigger-2

mutating-trigger-3

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:

mutating-trigger-4

mutating-trigger-5

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:

mutating-trigger-6

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:

mutating-trigger-7

When we try our delete statement again the rows are deleted, and the trigger’s message is displayed:

mutating-trigger-8

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!

Extension of the Upgrade Period

Prices for upgrades will remain in effect until the end of the year. This will give all current Version 10 users the option of upgrading to the current version at a reasonable cost.

The table below shows the prices that will be in effect until December 31, 2014.

KeepTool Light 11 KeepTool Professional 11 KeepTool Enterprise 11
Full License 99€ 699€ 999€
Upgrade from HoraLight 10 39€ 379€ 499€
Upgrade from Hora 10,
ER Diagrammer 10 or Debugger 10
279€ 399€

Prices are exclusive of Value Added Tax.

Please request a quote – preferably mentioning your Version 10 license number — at support@keeptool.com.