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

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

Oracle PL/SQL error message: ORA-04091: table is mutating, trigger/function may not see it

Cause:

A trigger (or a user defined PL/SQL 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.

Solution:

Rewrite the trigger (or function) so it does not read that table.

Example:

CREATE OR REPLACE TRIGGER update_salary
    BEFORE UPDATE OF salary ON employees
    REFERENCING OLD AS old
    NEW AS new
    FOR EACH ROW
DECLARE
	v_count number:=0;
BEGIN
	select count(1) 
	into v_count 
	from employees 
	where id = :NEW.ID 
	and salary is not null;
	
	if v_count > 0 then
		update employees 
		set old_salary = salary 
		where id = :NEW.ID;
	end if;
END;

update employees set SALARY=260 where id=1;

Output:

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

Correct

CREATE OR REPLACE TRIGGER update_salary
    BEFORE UPDATE OF salary ON employees
    REFERENCING OLD AS old
    NEW AS new
    FOR EACH ROW
BEGIN
  :NEW.old_salary := :OLD.salary;
END;

update employees set SALARY=260 where id=1;

Output:

1 rows updated.