CREATE OR REPLACE TRIGGER "PAYROLL TEST"."TRIG_BIUD_EMPLOYEE" AFTER INSERT OR DELETE OR UPDATE OF "ID" ON "PAYROLL TEST"."EMPLOYEE" FOR EACH ROW DECLARE RecordCount NUMBER; BEGIN IF UPDATING AND :old.id != :new.id THEN /* CASCADE UPDATES TO Pay Rate History */ UPDATE pay_rate SET pay_rate.employeeID = :new.id WHERE pay_rate.employeeID = :old.id; /* CASCADE UPDATES TO Employee Future Activities */ UPDATE emp_future SET emp_future.employeeID = :new.id WHERE emp_future.employeeID = :old.id; /* CASCADE UPDATES TO Employee History Info */ UPDATE emp_history SET emp_history.employeeID = :new.id WHERE emp_history.employeeID = :old.id; /* CASCADE UPDATES TO Employee License Table */ UPDATE emp_license SET emp_license.employeeID = :new.id WHERE emp_license.employeeID = :old.id; UPDATE emp_time SET emp_time.employeeID = :new.id WHERE emp_time.employeeID = :old.id; END IF; /* PREVENT DELETES IF DEPENDENT RECORDS IN Pay Rate History */ IF DELETING THEN SELECT COUNT(*) INTO RecordCount FROM pay_rate WHERE (pay_rate.EmployeeID = :old.id); IF RecordCount > 0 THEN raise_application_error(-20002, 'Cannot delete record. Pay rates have been found for this employee.'); END IF; DELETE FROM emp_future WHERE emp_future.employeeID = :old.id; DELETE FROM emp_history WHERE emp_history.employeeID = :old.id; DELETE FROM emp_license WHERE emp_license.employeeID = :old.id; DELETE FROM emp_time WHERE emp_time.employeeID = :old.id; END IF; END;