Chapter 10
Pages 238-39
Using Triggers for further audit

CREATE TABLE salary_change_log
(emp#           NUMBER(10)   NOT NULL
,transaction_dt DATE         NOT NULL
,user_name      VARCHAR2(30) NOT NULL
,old_sal_value  NUMBER(8,2)  NOT NULL
,new_sal_value  NUMBER(8,2)  NOT NULL
,CONSTRAINT scl_pk PRIMARY KEY (emp#, transaction_dt)
);

CREATE OR REPLACE TRIGGER pay_aur AFTER UPDATE ON payroll
FOR EACH ROW
BEGIN
  IF :old.emp#   <> :new.emp#
  THEN RAISE_APPLICATION_ERROR(-20000,
     'Illegal primary key change attempted on table PAYROLL'.;
  END IF;
  IF :old.salary <> :new.salary THEN
    BEGIN
       INSERT INTO salary_change_log ( emp#
                                     , transaction_dt
                                     , user_name
                                     , old_sal_value
                                     , new_sal_value
                                     )   VALUES
                                     ( :new.emp_no
                                     , SYSDATE
                                     , USER
                                     , :old.salary
                                     , :new.salary
                                     );
    END;
  END IF;
END pay_aur;
/