Creating a Trigger With an Exception Handler

Example: Creating a Database Trigger With an Exception Handler shows how to include an exception handler with a trigger. In this example, an exception is raised if an UPDATE operation changes the manager ID of an employee.

Creating a Database Trigger With an Exception Handler

-- create a temporary table
CREATE TABLE emp_except_log (emp_id NUMBER, mgr_id_new NUMBER, 
           mgr_id_old NUMBER, log_date DATE, action VARCHAR2(50));

CREATE OR REPLACE TRIGGER emp_log_update -- create a trigger
  BEFORE UPDATE ON employees FOR EACH ROW
DECLARE
  mgrid_exception EXCEPTION;
BEGIN
  IF (:NEW.manager_id <> :OLD.manager_id) THEN
      RAISE mgrid_exception;
  END IF;
  INSERT INTO emp_except_log (emp_id, mgr_id_new, mgr_id_old, log_date, action)
    VALUES (:NEW.employee_id, :NEW.manager_id, :OLD.manager_id, 
            SYSDATE, 'Employee updated');
EXCEPTION
  WHEN mgrid_exception THEN
    INSERT INTO emp_except_log (emp_id, mgr_id_new, mgr_id_old, log_date, action)
    VALUES (:NEW.employee_id, :NEW.manager_id, :OLD.manager_id, 
             SYSDATE, 'Employee manager ID updated!');
END;
/
-- update employees with the following UPDATE statements, firing trigger
UPDATE employees SET salary = salary * 1.01 WHERE employee_id = 105;
-- the trigger raises an exception with this UPDATE
UPDATE employees SET manager_id = 102 WHERE employee_id = 105;

-- view the log table
SELECT * FROM emp_except_log;