A row-level trigger that queries its own table, for example to compare a new salary with the highest salary, fails with ORA-04091: table is mutating. The table is in the middle of being changed by the statement, so its contents are not consistent, and Oracle forbids the read. The standard fix is a compound trigger that reads the table before the rows change.
Code for This Guide
The main examples are in the examples/triggers folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
The Error
This row-level trigger checks each new salary against the highest salary in EMPLOYEES, the table being updated.
Example:
create or replace trigger employees_cap_trg
before update of salary on employees
for each row
declare
v_max number;
begin
select max(salary) into v_max from employees; -- reads the table being changed
if :new.salary > v_max then
raise_application_error(-20101, 'Salary above the highest salary');
end if;
end;
/
update employees set salary = salary + 1 where employee_id = 161;Output:
Trigger EMPLOYEES_CAP_TRG compiled Error starting at line : 13 In command - update employees set salary = salary + 1 where employee_id = 161 Error at Command Line : 13 Column : 8 Error report - SQL Error: ORA-04091: table NIMBUS.EMPLOYEES is mutating, trigger/function may not see it ORA-06512: at "NIMBUS.EMPLOYEES_CAP_TRG", line 4 ORA-04088: error during execution of trigger 'NIMBUS.EMPLOYEES_CAP_TRG'
The UPDATE fails with ORA-04091, raised at the SELECT in the trigger. The example drops the trigger afterward.
Why It Happens
During a statement, a row-level trigger runs while some rows have already changed and others not. A query in the trigger would see a half-changed table, and the result could depend on the order rows are processed. Oracle therefore refuses queries and DML on the table from row-level triggers on it.
The Fix: a Compound Trigger
A compound trigger has several timing points that share variables. The BEFORE STATEMENT section reads the maximum once, before any row changes, and the BEFORE EACH ROW section checks each row against the saved value.
Example:
create or replace trigger employees_cap_trg
for update of salary on employees
compound trigger
v_max number;
before statement is
begin
-- read once, before the rows change
select max(salary) into v_max from employees;
end before statement;
before each row is
begin
if :new.salary > v_max then
raise_application_error(-20101, 'Salary above ' || v_max);
end if;
end before each row;
end employees_cap_trg;
/
update employees set salary = salary + 1 where employee_id = 161;
update employees set salary = 50000 where employee_id = 162;
rollback;Output:
Trigger EMPLOYEES_CAP_TRG compiled 1 row updated. Error starting at line : 21 In command - update employees set salary = 50000 where employee_id = 162 Error at Command Line : 21 Column : 8 Error report - SQL Error: ORA-20101: Salary above 48000 ORA-06512: at "NIMBUS.EMPLOYEES_CAP_TRG", line 13 ORA-04088: error during execution of trigger 'NIMBUS.EMPLOYEES_CAP_TRG' Rollback complete.
A small raise passes; raising a salary to 50,000, above the maximum of 48,000, is refused with the trigger's own error instead of ORA-04091. The example rolls back and drops the trigger.
Other Ways Out
- Use a statement-level trigger when the check concerns the table as a whole; statement triggers may query the table.
- Express the rule declaratively when possible: a CHECK constraint, or an assertion for rules across rows.
- Avoid autonomous transactions as a workaround: they read committed data, not the statement's changes, and give wrong results.
Related Guides
Conclusion
ORA-04091 means a row-level trigger tried to read or change its own table while a statement was modifying it. Read what you need once in the BEFORE STATEMENT section of a compound trigger and check rows against it, or move the rule into a statement trigger, a constraint, or an assertion.
