! Copyright © Oracle Corporation 1995.  All Rights Reserved.
! Trigger definitions for the sample personnel database
!
print "Defining triggers for the sample database"
!
! If an employee is terminated, remove all associated records from 
! the DEGREES, JOB_HISTORY, SALARY_HISTORY, and RESUMES relations.
!
DEFINE TRIGGER EMPLOYEE_ID_CASCADE_DELETE
  BEFORE ERASE
  FOR E IN EMPLOYEES EXECUTE
    FOR D IN DEGREES WITH
      D.EMPLOYEE_ID = E.EMPLOYEE_ID
      ERASE D
    END_FOR;
    FOR JH IN JOB_HISTORY WITH
      JH.EMPLOYEE_ID = E.EMPLOYEE_ID
      ERASE JH
    END_FOR;
    FOR R IN RESUMES WITH
      R.EMPLOYEE_ID = E.EMPLOYEE_ID
      ERASE R
    END_FOR;
    FOR SH IN SALARY_HISTORY WITH
      SH.EMPLOYEE_ID = E.EMPLOYEE_ID
      ERASE SH
    END_FOR;
! Also, if an employee is terminated and that employee 
! is the manager of a department, set the manager_id 
! missing for that department. 
    FOR D IN DEPARTMENTS WITH D.MANAGER_ID = E.EMPLOYEE_ID
      MODIFY D USING D.MANAGER_ID = 
        RDB$MISSING (D.MANAGER_ID)  END_MODIFY
    END_FOR
  FOR EACH RECORD.
!
! If the status_code in WORK_STATUS relation changes, make the
! change in all affected EMPLOYEES records also.
!
DEFINE TRIGGER STATUS_CODE_CASCADE_UPDATE
  BEFORE MODIFY OF STATUS_CODE NEW CONTEXT NEW_WS
  FOR OLD_WS IN WORK_STATUS EXECUTE
    FOR E IN EMPLOYEES 
       WITH E.STATUS_CODE = OLD_WS.STATUS_CODE
    MODIFY E USING 
       E.STATUS_CODE = NEW_WS.STATUS_CODE 
       END_MODIFY
    END_FOR
  FOR EACH RECORD.

!
! If the college_code for a particular college is changed, make
! the change in all affected records in the DEGREES relation also.
!
DEFINE TRIGGER COLLEGE_CODE_CASCADE_UPDATE
  BEFORE MODIFY OF COLLEGE_CODE NEW CONTEXT NEW_COL
  FOR OLD_COL IN COLLEGES EXECUTE
    FOR D IN DEGREES 
       WITH D.COLLEGE_CODE = OLD_COL.COLLEGE_CODE
    MODIFY D USING 
       D.COLLEGE_CODE = NEW_COL.COLLEGE_CODE 
       END_MODIFY
    END_FOR
  FOR EACH RECORD.
!
COMMIT