>--First trigger >BEGIN > pkg_TriggerFunctions.v_MyVar := True; > --process updates. > pkg_TriggerFunctions.v_MyVar := False; >END; >--------- > >--Second trigger (or whatever) > BEGIN > IF pkg_TriggerFunctions.v_MyVar THEN > --Special trigger processing > ELSE > --Normal trigger processing > END IF; >END; >>
>>CREATE OR REPLACE TRIGGER "PAYROLL TEST"."TRIG_BIUD_EMP_TIME" >> BEFORE INSERT OR >> DELETE OR >> UPDATE OF "DATEFROM", "DATETO", "EMPLOYEEID" >> ON "PAYROLL TEST"."EMP_TIME" >> FOR EACH ROW >> DECLARE RecordCount NUMBER; >>BEGIN >> /* PREVENT INSERTS AND UPDATES IF NO MATCHING KEY IN 'employee' */ >> IF (UPDATING AND :old.employeeid != :new.employeeid) OR INSERTING >> THEN >> SELECT COUNT(*) >> INTO RecordCount >> FROM employee >> WHERE (employee.id = :new.employeeid); >> IF RecordCount = 0 >> THEN >> raise_application_error(-20003, 'Employee ID does not exist in the employee table.'); >> END IF; >> END IF; >>END; >>>>
>>>>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; >>>>