Oracle Mutating Table Error – Solved with KeepTool

Updated October 2025

---

Introduction

The Mutating Table Trigger Wizard in KeepTool’s Hora application provides an elegant way to handle one of the most common Oracle errors:

ORA-04091: table … is mutating, trigger/function may not see it.

This error typically occurs when a trigger or function tries to read or modify a table that is already being changed by the same DML statement.

---

Why the Error Happens

In many real-world database designs, developers want to maintain summary or aggregate data in master tables.
For example, imagine a column AVERAGE_SALARY in the DEPARTMENTS table that should automatically update whenever an employee’s salary changes in EMPLOYEES.

If you try to do this with a row-level trigger, Oracle raises the mutating table error, because the trigger attempts to query the same table that is still being modified.

---

Using the Mutating Table Trigger Wizard

Hora’s Mutating Table Trigger Wizard simplifies this problem by automatically creating an Oracle compound trigger.
You can start the wizard from the Schema Browser by right-clicking a table and selecting “Mutating Table Trigger Wizard.”

The wizard detects the relationships between master and detail tables and proposes suitable defaults.
In our example, we renamed the trigger from EMPLOYEES_CM to UPDATE_AVERAGE_SALARY_CM, chose the aggregate function (e.g., AVG), and selected the column to aggregate (SALARY).
Since the EMPLOYEES table relates to multiple master tables, we ensured the correct one (DEPARTMENTS) was selected.

After confirming with OK and keeping SQL Preview enabled, Hora automatically generates the corresponding compound trigger code.

---

Understanding the Generated Trigger

The generated compound trigger is divided into several sections:

• Declaration Section – defines collections or variables used during processing.

• BEFORE EACH ROW Section – collects affected DEPARTMENT_ID values as rows are modified.

• AFTER STATEMENT Section – executes once all DML operations are finished, calculates aggregate results, and updates the master table.

Because the update of the master table happens after all detail rows are processed, the mutating table condition never occurs.

You can also enhance the generated code by adding custom statements, for example:

Summary

With the Mutating Table Trigger Wizard, KeepTool provides a fast, reliable way to resolve ORA-04091 errors.
The wizard automatically builds a fully functional compound trigger, ensuring that updates to master tables occur safely after row-level operations.

This feature not only prevents mutating table errors but also saves significant development time when managing DELETE, UPDATE, and INSERT triggers in Oracle databases.

---