Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page