-- 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;
  emp_history_records NUMBER;
BEGIN
  IF INSERTING THEN
    INSERT INTO emp_history (id,start_date)
           VALUES (:new.id,sysdate);
  END IF;
  IF DELETING THEN
    SELECT COUNT(*)
      INTO emp_history_records
      FROM emp_history
     WHERE id = :old.id;
    IF emp_history_records > 0 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;
    ELSE
      INSERT INTO emp_history (id,end_date)
             VALUES (:old.id,sysdate);
    END IF;
  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
/
