OracleVillage

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.
NOTE: A table is never considered mutating for STATEMENT triggers.

The trigger table itself is mutating table, as well as any table referencing it with FOREIGN KEY constraint. This restriction prevents a row trigger from seeing inconsistent set of data.

Example:
CREATE OR REPLACE TRIGGER check_sal
BEFORE INSERT OR UPDATE OF sal, job
ON emp
FOR EACH ROW
DECLARE
v_min_sal emp.sal%TYPE;
v_max_sal emp.sal%TYPE;
BEGIN
SELECT MIN (sal), MAX (sal)
INTO v_min_sal, v_max_sal
FROM emp
WHERE job = :new.job;

IF :new.sal < v_min_sal OR :new.sal > v_max_sal
THEN
raise_application_error (-20505, 'Out of Range');
END IF;
END;
/

Lets try to update EMP sal column...

update emp
set sal =1500
where ename ='SMITH';


We will show you the Mutating error work around in next post.