Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
16-30.	Triggers of the same type fire in the order of their respective object identifiers (OIDs). OIDs, which are assigned by Oracle when the trigger is created, are beyond the designer’s control. Consequently, the order of firing triggers is not guaranteed and cannot be controlled. The best approach is to make sure that the trigger design is independent of the order of trigger firing.
Q:
16-31.	The trigger is clearly designed to fire for DML update events and only when the new salary doesn’t equal the old salary. The way the trigger is written at present, it fires unnecessarily across a wide range of DML events that occur on the employee table. You can use the WHEN clause to eliminate these unnecessary executions: CREATE OR REPLACE TRIGGER employee_upd_t1 AFTER UPDATE OF salary ON employee FOR EACH ROW WHEN (old.salary <> new.salary) BEGIN employee_pkg.update_emp (:new.employee_id, :new.salary); END;
Q:
16-32.	At first, you might be tempted to try something like this: CREATE OR REPLACE TRIGGER employee_t1 BEFORE DELETE ON employee FOR EACH ROW BEGIN UPDATE employee SET mgr = null WHERE mgr = :new.empno; END; / Unfortunately, this trigger results in the mutating trigger error: ORA-04091 table name is mutating, trigger/function may not see it You can use a combination of packaged variables and different types of triggers to solve this problem. The first step is to create a package containing an index-by table to hold the IDs of the managers who have been deleted: CREATE OR REPLACE PACKAGE ...