In addition to the PL/SQL errors that apply to other subprogram types, there are additional potential problems for triggers. Because triggers fire in response to a database event they need to be considered in the context of a transaction. A mutating table exception occurs if row-level trigger code references the triggering table in a query.
CREATE OR REPLACE TRIGGER EMP_MUTATION AFTER INSERT OR UPDATE OF SALARY ON EMPLOYEES FOR EACH ROW DECLARE v_salary employees.salary%type; BEGIN SELECT min(salary) into v_salary from employees; IF :new.salary < v_salary THEN raise_application_error(-20001,'Salary too low'); END IF; END; /
This trigger is used to demonstrate a mutating table error. Notice that the MIN function is checking every row in the employees table. This check occurs at the same time that the DML that fired the trigger is occurring.
insert into employees ( employee_id, first_name, LAST_NAME, EMAIL, hire_date, JOB_ID) values ( 999, 'Joe', 'Mutant', 'N/A', sysdate, 'AD_PRES' ); /
Running this insert statement causes the mutating table error to occur. There are a number of possible solutions for this type of problem - but the fundamental lesson is to limit the use of triggers to discrete well understood units of work that do not result in such side effects.
There are a number of ways of dealing with mutating table errors. One solution is the use of PRAGMA AUTONOMOUS_TRANSACTION. However, in general, it is best to reconsider trigger design if such errors occur. Triggers can frequently be rewritten (in conjunction with other PL/SQL programs) to accomplish the intended goal without resorting to solutions that operate outside of the immediate transactional context.