-- paylog.sql

-- create the pay_change_log table and pay_change view 
--(if they don't exist)

DROP TABLE pay_change_log
/
CREATE TABLE pay_change_log
(
   id NUMBER,
   old_amount NUMBER(9,2),
   new_amount NUMBER(9,2),
   date_changed DATE,
   changed_by VARCHAR2(30)
)
/
CREATE OR REPLACE TRIGGER pay_change
 AFTER INSERT OR UPDATE OF pay_amount
    ON employee
   FOR EACH ROW
DECLARE
   chdate DATE;
   chuser VARCHAR2(30);
BEGIN
   chdate := SYSDATE;
   chuser := USER;
   
   IF INSERTING THEN
     INSERT INTO pay_change_log
     VALUES ( :new.id, NULL, :new.pay_amount, chdate, chuser);
   ELSE
     INSERT INTO pay_change_log
     VALUES ( :new.id, :old.pay_amount, :new.pay_amount, chdate, 
              chuser);
   END IF;
END;
/
