-- emp_history.sql

DROP TABLE emp_history
/

CREATE TABLE emp_history(
   id  NUMBER,
   start_date DATE,
   end_date DATE,
   ending_salary NUMBER(9,2),
   pay_type_code CHAR(1),
   length_of_employment INTERVAL DAY TO SECOND
)
/

CREATE OR REPLACE TRIGGER emp_hist_trigger
  AFTER INSERT OR DELETE ON employee
  FOR EACH ROW
DECLARE
  time_employed INTERVAL DAY TO SECOND;
  hire_date DATE;
BEGIN
  IF INSERTING THEN
    INSERT INTO emp_history (id,start_date)
           VALUES (:new.id,sysdate);
  END IF;
  IF DELETING THEN
    SELECT start_date
      INTO hire_date
      FROM emp_history
     WHERE id = :old.id
       AND end_date IS NULL;
    time_employed := numtodsinterval(sysdate - hire_date, 'day');
    UPDATE emp_history SET end_date = SYSDATE, ending_salary
           = :old.pay_amount, length_of_employment = time_employed
     WHERE id = :old.id;
  END IF;
END;
/

INSERT INTO employee (id, store_number, title, pay_type_code, 
                      pay_amount, supervisor_id)
       VALUES (6601, 7, 'Cool Clerk', 'H', 9.25, 7881)
/

DELETE FROM employee
 WHERE id = 6601
/

SELECT * FROM emp_history
/
