Appendix B
Pages 493-94

Example B-3

The Solution Extended to Handle Cascade Operations

CREATE OR REPLACE PACKAGE pk_emp_mgr AS
   in_cascade_ind BOOLEAN := FALSE;
   PROCEDURE clear_count;
   PROCEDURE add_mgr (p_empno IN emp.empno%TYPE);
   PROCEDURE nullify_reporting_emps;
END pk_emp_mgr;
/

CREATE OR REPLACE TRIGGER emp_bd BEFORE DELETE ON emp
BEGIN
    IF NOT pk_emp_mgr.in_cascade_ind THEN
      pk_emp_mgr.clear_count;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER emp_ad AFTER DELETE ON emp
BEGIN
  IF NOT pk_emp_mgr.in_cascade_ind THEN
      pk_emp_mgr.nullify_reporting_emps;
  END IF;
END;
/

CREATE OR REPLACE TRIGGER dept_bd BEFORE DELETE ON dept
BEGIN
   pk_emp_mgr.in_cascade_ind := TRUE;
   pk_emp_mgr.clear_count;
END;
/

CREATE OR REPLACE TRIGGER dept_ad AFTER DELETE ON dept
BEGIN
   pk_emp_mgr.nullify_reporting_emps;
   pk_emp_mgr.in_cascade_ind := FALSE;
END;
/

DELETE FROM dept WHERE deptno = 10;

SELECT * FROM EMP;

>EMPNO    DEPTNO ENAME      JOB             MGR
>--------- --------- ---------- --------- ---------
>      123        11 Little Guy Janitor
>
ROLLBACK;
DELETE FROM EMP WHERE EMPNO = 111;
SELECT * FROM EMP;
/* should give same results */
ROLLBACK;