-- comments.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
)
/
/*
Author: I.T. Author
Date:   1/1/05
This trigger is executed after every insert or delete
on an employee record.
*/
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         --only if we're inserting new record
    INSERT INTO emp_history(id,start_date)
    VALUES(:new.id,sysdate);
  END IF;
  IF DELETING THEN          --only if we're deleting a record
    SELECT start_date
      INTO hire_date
      FROM emp_history
     WHERE id = :old.id;
    -- Use numtodsinterval function to convert from numeric to interval
    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;
/

/* Test our trigger by creating a new employee */
INSERT INTO employee (id, store_number, title, pay_type_code, 
       pay_amount, supervisor_id)
VALUES (6601, 7, 'Cool Clerk', 'H', 9.25, 7881);

/* Then fire them */
DELETE FROM employee
 WHERE id = 6601;

-- Let's see if they were recorded
SELECT * FROM emp_history;

