Oracle PL/SQL Einzel-Tipp ansehen

TRIGGER

Tipp 80

DROP TABLE scott.emp_trig;
CREATE TABLE scott.emp_trig AS SELECT * FROM scott.emp;
col cmode format A6
col user_name format a10
col sal format 9999

DROP TABLE scott.emp_audit;
CREATE TABLE scott.emp_audit AS SELECT * FROM scott.emp WHERE 1=2;

ALTER TABLE scott.emp_audit ADD
    ( old_sal         NUMBER(7,2),
      new_sal         NUMBER(7,2),
      user_name     VARCHAR2(30),
      change_date     DATE,
      event            CHAR(1));

CREATE OR REPLACE TRIGGER scott.emp_trig_idu
BEFORE INSERT OR UPDATE OR DELETE ON scott.emp_trig
FOR EACH ROW
DECLARE
v_event CHAR(1):='X';
BEGIN
  IF INSERTING THEN
      v_event:='I';
  ELSIF UPDATING THEN
      v_event:='U';
  ELSE --DELETING
      v_event:='D';
  END IF;
 
  INSERT INTO scott.emp_audit
  (empno,old_sal,new_sal,user_name,change_date,event)
  VALUES
  (:OLD.empno,:OLD.sal,:NEW.sal,user,sysdate,v_event);
END;
/