-- Demonstrate multi-function (INSERT,UPDATE,DELETE) trigger, use of :new and :old,
-- and simple debugging using DBMS_OUTPUT.PUT_LINE
-- Copyright (c) 2004, Caleb.com

-- Must enable server output for SQL*Plus first
SET SERVEROUT ON SIZE 10000

-- Trigger will fire for any DML on table
CREATE OR REPLACE TRIGGER emp_dml 
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
   IF INSERTING THEN
      -- during insert only :new is available
      DBMS_OUTPUT.PUT_LINE('Inserting "'||:new.ENAME||'"');
   ELSIF UPDATING THEN
      -- during update both :new and :old are available
      DBMS_OUTPUT.PUT_LINE('Updating "'||:old.ENAME||'" to "'||:new.ENAME||'"');
   ELSIF DELETING THEN
      -- during delete only :old is available
      DBMS_OUTPUT.PUT_LINE('deleteing "'||:old.ENAME||'"');
      IF (:old.ENAME='KING') THEN
         raise_application_error(-20002,'Can''t delete Top Dog!');
      END IF;
   END IF;
END;
/