-- Copyright © Oracle Corporation 1995.  All Rights Reserved.
-- Trigger definitions for the sample personnel database
--
print 'Creating triggers for the sample database';
--
-- If an employee is terminated, remove all associated rows from the 
-- DEGREES, JOB_HISTORY, and SALARY_HISTORY tables.
--
CREATE TRIGGER EMPLOYEE_ID_CASCADE_DELETE
  BEFORE DELETE ON EMPLOYEES
  (DELETE FROM DEGREES D WHERE D.EMPLOYEE_ID = 
   EMPLOYEES.EMPLOYEE_ID)
     FOR EACH ROW
  (DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = 
   EMPLOYEES.EMPLOYEE_ID)
     FOR EACH ROW
  (DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID = 
   EMPLOYEES.EMPLOYEE_ID)
     FOR EACH ROW
-- Also, if an employee is terminated and that employee 
-- is the manager of a department, set the manager_id 
-- null for that department.
  (UPDATE DEPARTMENTS D  SET D.MANAGER_ID = NULL
   WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
     FOR EACH ROW;

--
-- If the status_code in WORK_STATUS table changes, make 
-- the change in all affected EMPLOYEES rows also.
--
CREATE TRIGGER STATUS_CODE_CASCADE_UPDATE
  BEFORE UPDATE OF STATUS_CODE ON WORK_STATUS
     REFERENCING OLD AS OLD_WORK_STATUS 
                 NEW AS NEW_WORK_STATUS
  (UPDATE EMPLOYEES E  
   SET E.STATUS_CODE = NEW_WORK_STATUS.STATUS_CODE
   WHERE E.STATUS_CODE = OLD_WORK_STATUS.STATUS_CODE) 
     FOR EACH ROW;

--
-- If the college_code for a particular college is changed, make
-- the change in all affected rows in the DEGREES table also.
--
CREATE TRIGGER COLLEGE_CODE_CASCADE_UPDATE
  BEFORE UPDATE OF COLLEGE_CODE ON COLLEGES
     REFERENCING OLD AS OLD_COL  
                 NEW AS NEW_COL
  (UPDATE DEGREES D  
   SET D.COLLEGE_CODE = NEW_COL.COLLEGE_CODE
   WHERE D.COLLEGE_CODE = OLD_COL.COLLEGE_CODE)
     FOR EACH ROW;

--
COMMIT;