Trigger Errors

Contact Us or call 1-877-932-8228
Trigger Errors

Trigger Errors

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.

Code Sample:

Database-Triggers/Demos/mutating_trigger.sql
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;
/

Code Explanation

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.

Code Sample:

Database-Triggers/Demos/mutating_trigger_test.sql
insert into employees (
employee_id,
first_name,
LAST_NAME,
EMAIL,
hire_date,
JOB_ID)
values 
(
 999,
 'Joe', 
 'Mutant',
 'N/A',
 sysdate,
 'AD_PRES'
);
/

Code Explanation

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.

Next