Oracle and PL/SQL Recipes: A Problem-Solution Approach

5-11. Firing Two Triggers on the Same Event

Problem

There is a requirement to create a trigger to enter the SYSDATE into the HIRE_DATE column of the LOCATIONS table. However, there is already a trigger in place that is fired BEFORE INSERT on the table, and you do not want the two triggers to conflict.

Solution

Use the FOLLOWS clause to ensure the ordering of the execution of the triggers. The following example shows the creation of two triggers that are to be executed BEFORE INSERT on the EMPLOYEES table.

First, we'll create a trigger to verify that a new employee's salary falls within range:

CREATE OR REPLACE TRIGGER verify_emp_salary BEFORE INSERT ON employees FOR EACH ROW DECLARE   v_min_sal     jobs.min_salary%TYPE;   v_max_sal     jobs.max_salary%TYPE; ...