Mutating table error
Mutating problem occurs if you try to access a table from a trigger which is fired by a command that modified the table. For example, if you are updating EMPLOYEES table and try to access EMPLOYEES table using SELECT statement in before or after row-level trigger then Oracle complains that the table is being modified so it cannot be read from the trigger.
Let us understand the problem with an example. Assume we have to ensure the salary of manager is always more or equal to the salaries of the employees whom he/she manages. In HR account of Oracle, we have EMPLOYEES table containing employee_id, salary and manager_id columns (apart from other not related to this example). We have to ensure whenever we are inserting or updating a row in EMPLOYEES table the salary of the employee is less than or equal to the salary of the manager, if manager_id is not null.
Trigger with mutating problem
Let us try to create a trigger to accomplish the task as follows:
create or replace trigger trg_check_salary
for each row
if :new.manager_id is not null then
/* get the salary of the manager */
select salary into v_salary
where employee_id = :new.manager_id;
if v_salary < :new.salary then
raise_application_error(20100,’Employee salary is more than manager’);
Now issue the following command to update EMPLOYEES table as follows and see the message issued by Oracle.
SQL> update employees set salary = 10000 where employee_id = 110;
update employees set salary = 10000 where employee_id = 110
ERROR at line 1:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at “HR.TRG_CHECK_SALARY”, line 6
ORA-04088: error during execution of trigger ‘HR.TRG_CHECK_SALARY’
As we understood, the problem is, we are trying to access the table (EMPLOYEES) that is being modified by the UPDATE command.
-Mutating table error can be resolved by Statement level trigger.
-Using pragma autonomous_transaction on a procedure/function called from trigger or in trigger itself
-If you need to update a mutating table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers – an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.