Chapter 10
Page 240
Example 10-1

An Audit Table and Trigger to Capture the Information

CREATE TABLE salary_change_log
(emp#           NUMBER(10)   NOT NULL
,transaction_dt DATE         NOT NULL
,user_name      VARCHAR2(30) NOT NULL
,dml_type       CHAR(1)      NOT NULL
 CONSTRAINT scl_dml_type CHECK (dml_type IN ('I', 'U', 'D'))
,old_sal_value  NUMBER(8,2)
,CONSTRAINT scl_old_sal
   CHECK (  DECODE(dml_type, 'I', 0, 'U', 1, 'D', 1, 0)
          = DECODE(old_sal_value, null, 0, 1) )
,new_sal_value  NUMBER(8,2)
,CONSTRAINT scl_new_sal
   CHECK (  DECODE(dml_type, 'I', 1, 'U', 1, 'D', 0, 0)
          = DECODE(new_sal_value, null, 0, 1) )
,CONSTRAINT scl_pk PRIMARY KEY (emp#, transaction_dt)
);

CREATE OR REPLACE TRIGGER pay_aud
AFTER INSERT OR UPDATE OR DELETE ON payroll
FOR EACH ROW
DECLARE
  l_dml_typ salary_change_log.dml_type%TYPE;

BEGIN
  IF :old.emp#   <> :new.emp#  - fails if either is null
  THEN RAISE_APPLICATION_ERROR(-20000,
     'Illegal primary key change attempted on table PAYROLL');
  END IF;
  IF UPDATING ('SALARY') THEN
    l_dml_typ := 'U';
  ELSIF INSERTING THEN
  ELSIF INSERTING THEN
    l_dml_typ := 'I';
  ELSE - deleting
    l_dml_typ := 'D';
  END IF;
  IF l_dml_typ IS NOT NULL
  THEN INSERT INTO salary_change_log
                       ( emp#
                       , transaction_dt
                       , user_name
                       , dml_type
                       , old_sal_value
                       , new_sal_value
                       )   VALUES
                       ( :new.emp#
                       , SYSDATE
                       , USER
                       , l_dml_typ
                       , :old.salary
                       , :new.salary
                       );
  END IF;
END pay_aud;
/