Mutating table error

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

before update

on employees

for each row

declare

v_salary employees.salary%type;

begin

if  :new.manager_id is not null then

/* get the salary of the manager */

select  salary into v_salary

from employees

where employee_id = :new.manager_id;

 

if  v_salary < :new.salary then

raise_application_error(20100,’Employee salary is more than manager’);

end if;

end if;

end;

/

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.

 

Resolution

-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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s