CREATE OR REPLACE PACKAGE BODY pk emp mgr AS

TYPE tab empno type IS TABLE OF emp.empno%TYPE INDEX BY BINARY INTEGER; 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 BEFOR DELETE ON emp BEGIN

pk emp mgr.clear count; END;

CREATE TRIGGER emp bdr BEFOR DELETE ON emp

FOR EACH ROW

BEGIN

p k 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 Litle Guy Janitor : >

ROLLBACK;

Хотя все это может показаться набором большого числа несвязанных фрагментов кода, это проверенный и эффективный метод. Однако здесь есть небольшой нюанс.

Что произойдет, если строки из таблицы ЕМР удаляются не в результате выдачи пользователем предложения DELETE для этой таблицы, а вследствие каскадного удаления из главной таблицы? Давайте введем в наш пример столь же вездесущую таблицу DEPT, закрепим всех служащих за соответствующими подразделениями, а затем удалим подразделение, в котором работает начальник (Big Boss). В результате Big Boss будет удален и все его подчиненные лишаться начальника. Скрипт для проверки этого механизма показан в примере Б.2.

Пример Б.2. Проблема мутирующей таблицы возникла вновь - из-за каскадного удаления из главной таблицы

CREATE TABLE dept ( deptno NUMBER(4) NOT NULL

, dname VARCHAR2(20) NOT NULL , loc VARCHAR2(20) NOT NULL , CONSTRAINT dept p)< PRIMARY KEY (deptno) ) ;

INSERT INTO dept VALUES(10, DIRECT SALES, BOSTON); INSERT INTO dept VALUES(11, CLEANING, NEWARK); ALTER TABLE emp ADD CONSTiyINT emp f)<l FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

DELETE FROM dept WHERE deptno = 10;

>ERROR at line 1:

>ORA-04091:таблица SCOTT.EMP мутирует, триггер/функция может ее не увидеть >ORA-06512: в SCOTT.PK EMP MGR , строка 19 >ORA-06512:b строке 2

>ORA-04088;ошибка во время выполнения триггера SCOTT.EMP AD

Удивительно, но проблема мутирующей таблицы возникла вновь, однако теперь о ней сообщается в триггере уровня оператора. Причина в том, что Oracle рассматривает таблицу как мутирующую (находящуюся в несогласованном состоянии) во время выполнения рекурсивного SQL. Рекурсивный SQL - это любое SQL-предложение, которое генерируется самой Oracle при выполнении другого SQL-предложения. В данном случае Oracle сгенерировала предложение DELETE для таблицы ЕМР при выполнении операции ON DELETE CASCADE.

Таким образом, мы выявили еще одну проблему, поэтому давайте решать и ее. Нам нужно создать для таблицы DEPT такие же триггеры уровня операторов, как и для таблицы ЕМР, чтобы они срабатывали и при удалении в этой таблице, а также предотвратить срабатывание кода уровня операторов для таблицы ЕМР, если он вызывается в результате рекурсивного SQL. Решение приведено в примере Б.З. В заголовок пакета включена глобальная переменная {incascadejnd), которая будет сообщать, находимся мы в режиме ON DELETE CASCADE или нет. Триггеры уровня операторов для ЕМР

изменены таким образом, чтобы они не срабатывали, если эта переменная пакета установлена. Также определены триггеры BEFORE DELETE и AFTER DELETE для таблицы DEPT, назначение которых - установка и сброс глобальной переменной пакета, а также запуск процедуры nullify report-ingjemps. Все новые и исправленные фрагменты в примере Б.З выделены жирным шрифтом.

Пример Б 3. Решение, в котором учтены каскадные операции

CREATE OR REPLACE PACKAGE pk emp mgr AS in cascade ind BOOLEAN := FALSE;

PROCEDDRE clear count;

PROCEDDRE add mgr (p empno IN emp.empno%TYPE);

PROCEDURE nullity reporting emps; END pk emp mgr; /

CREATE OR REPLACE TRIGGER emp bd BEFOR DELETE ON emp BEGIN

IF NOT pk en 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 en mgr.in cascade ind THEN

pk emp mgr.nullify reporting emps; END IF; END;

CREATE OR REPLACE TRIGGER dept bd BEFOR 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 >---------

> 123 >

ROLLBACK;

DEPTNO ENAME JOB

II Litle Guy Janitor