Appendix B
page 490

Example B-1

Avoiding Mutating Tables by Deferring Action to Statement Level

DROP TRIGGER emp_ad;

CREATE OR REPLACE PACKAGE pk_emp_mgr as
   PROCEDURE clear_count;
   PROCEDURE add_mgr (p_empno IN emp.empno%TYPE);
   PROCEDURE nullify_reporting_emps;
END pk_emp_mgr;
/
CREATE OR REPLACE PACKAGE BODY pk_emp_mgr AS
   TYPE tab_empno_type IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTE\
GER;
   g_empno tab_empno_type;
   g_empno_ind BINARY_INTEGER;

PROCEDURE clear_count IS
BEGIN
   g_empno_ind := 0;
END clear_count;

PROCEDURE add_mgr(p_empno IN EMP.EMPNO%type) Is
BEGIN
   g_empno_ind := g_empno_ind + 1;
   g_empno(g_empno_ind) := p_empno;
END add_mgr;

PROCEDURE nullify_reporting_emps IS
l_empno emp.empno%TYPE;
BEGIN
   FOR I IN 1..g_empno_ind LOOP
      l_empno := g_empno(g_empno_ind);
      UPDATE emp SET mgr = NULL WHERE mgr = l_empno;
   END LOOP;
END nullify_reporting_emps;
END pk_emp_mgr;
/

CREATE TRIGGER emp_bd BEFORE DELETE ON emp
BEGIN
   pk_emp_mgr.clear_count;
END;
/

CREATE TRIGGER emp_bdr BEFORE DELETE ON emp
FOR EACH ROW
BEGIN
   pk_emp_mgr.add_mgr(:old.empno);
END;
/

CREATE TRIGGER emp_ad AFTER DELETE ON emp
BEGIN
   pk_emp_mgr.nullify_reporting_emps;
END;
/

DELETE emp WHERE empno = 111;
SELECT * FROM emp;

>EMPNO    DEPTNO ENAME      JOB             MGR
>--------- --------- ---------- --------- ---------
>      123        11 Little Guy Janitor
>
ROLLBACK;