является рефлекторным. Мы не можем ввести это отношение декларативно (ограничением PRIMARY KEY), потому что Oracle? поддерживает только режимы RESTRICT (по умолчанию) и ON DELETE CASCADE. Поэтому мы разрабатываем третий режим, ON DELETE NULLIFY, который некоторые другие реляционные СУБД поддерживают как стандартный. Учтите, что каждый раз, когда мы не задаем (или не можем задать) ограничения для определения отношений, мы должны писать собственный код, вводяший все правила.

Давайте сначала возьмем очевидное решение и посмотрим, почему оно не работает. Пример, иллюстрирующий это решение, приведен ниже. Триггер emp bdr не сработал из-за ошибки, связанной с мутирующей таблицей: он не может получить доступ к таблице, вызвавшей срабатывание триггера (в данном случае это таблица ЕМР).

CREATE TABLE emp ( empno NUMBER(4) NOT NULL

, deptno NUMBER(4) NOT NULL

, ename VARCHAR2(10)

, job VARCHAR2(9)

, mgr NUMBER(4)

, CONSTRAINT emp pk PRIMARY KEY (empno));

INSERT INTO emp VALUES(111, 10, Big Boss, Manager , NULL); INSERT INTO emp VALUES(123, 11, Little Guy, Janitor, 111);

CREATE OR REPLACE TRIGGER emp bdr BEFOR DELETE ON emp

FOR EACH ROW

BEGIN

UPDATE emp SET mgr = NULL WHERE mgr = :old.empno; END;

DELETE emp WHERE empno = 111;

/*Возникает следующая ошибка: ERROR at line 1:

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

ORA-04088:ошибка во время выполнения триггера SCOTT.EMP BDR*/

Каковы же причины появления ошибки? У оператора SET и у операторов, которые работают со множеством строк, есть общее, причем довольно теоретическое, свойство. SQL не гарантирует порядок обработки строк, указанных в таком операторе. Из этого следует, что если известно, что таблица мутирует во время выполнения оператора SET, то любая ссьшка на эту таблицу в триггере может даваться до или после того, как фактически гфоизойдет изменение таблицы. Поскольку порядок, в котором вносятся изменения, не гарантирован, то не обязательно, что при каждом выполнении идентичных операторов с идентичными данными вы получите одни и те же результаты.

Как же преодолеть это ограничение и добиться того, что вы собрались сделать? Мы видели, как эту проблему пытались решить самыми разными

способами - например, с помощью зеркальных таблиц или выделенного серверного процесса, который ждет сигнала на выполнение обновления, приводящего к ошибке. Эти решения очень сложны и опасны, поэтому мы предлагаем вам два простых метода выхода из данной ситуации.

Сначала рассмотрим метод, который крайне неэффективен и, следовательно, не рекомендован, но работает. Вы ждете, пока будет выполнено удаление, а затем просматриваете всю таблицу на предмет наличия строк, в которых столбец MGR содержит ссылку на несуществующего служащего. Если такие строки есть, вы обнуляете в них значение столбца MGR. Как это реализовать, показано в ниже.

DROP TRIGGER emp bdr;

CREATE OR REPLACE TRIGGER emp ad AFTER DELETE ON emp BEGIN

UPDATE emp

SET mgr = NULL

WHERE NOT EXISTS (SELECT NULL

FROM emp emp2 WHERE emp2.empno = emp.mgr);

END;

DELETE emp WHERE empno = 111;

SELECT * FROM emp;

>

> EMPNO DEPTNO ENAME JOB MGR >----------------------------------------------

> 123 11 Litle Guy Janitor

>

ROLLBACK;

Несмотря на некоторую неэффективность, это решение обладает одним интересным свойством: оно устраняет предыдущие ошибочные ссылки, которые могли появиться вследствие выполнения программы в течение нескольких часов (или недель) со случайно отвогюченным триггером. При первом же удалении любого служащего запустится триггер, который обнулит значения столбца MGR, соответствующие несуществующему начальнику, для всех служащих, а не только для тех, начальники которых удалены в текущем операторе.

Такое самоисправление данных, на первый взгляд, может показаться очень хорошим делом, но оно влечет за собой нежелательные последствия. Каждый раз, когда удаляется один служащий, вся таблица ЕМР просматривается на предмет поиска служащих, работающих у несуществующего начальника. Для взятой в качестве примера таблицы с двумя строками это не важно, но для таблицы с несколькими тысячами строк производительность будет ужасно низкой. Мы должны каким-то образом ограничить поиск только теми строками, которые содержат ссылки на удаленного начальника, не создавая при этом проблему мутирующей таблицы.

Какие же еще методы можно использовать для разрешения этой проблемы? Второй (рекомендуемый) метод предусматривает умный трюк - сохранить удаленных служащих при помощи триггеров уровня строки и отложить поиск служащих, у которых удаленный служащий был начальником, до триггера AFTER уровня предложения. Для временного хранения удаленных служащих нужно использовать глобальную таблицу PL/SQL.

Что такое глобальная таблица PL/SQL? Пакеты PL/SQL могут содержать глобальные переменные. Они объявляются либо в пакете (если должны быть доступны извне), либо в теле пакета, но вне всех процедур и функций. В последнем случае их можно видеть только из данного пакета. Переменные обоих типов создаются при первом обращении к пакету (по любой причине) и остаются в памяти до тех пор, пока сеанс Oracle не разорвет соединение. PL/SQL версии 2.3 позволяет очищать таблицы, но для любого узла, на котором используется Oracle версии 7.2 и меньше, постепенное увеличение памяти для глобальных переменных пакетов может стать проблемой, которую необходимо учитывать при проектировании. В качестве одной из мер очищаемой таблице можно присвоить пустую таблицу такого же типа.

Код для реализации решения с помощью глобальных переменных пакета приведен в примере Б.1. Этот код состоит из пакета с тремя процедурами и трех триггеров на таблице ЕМР, каждый из которых запускается разным событием и вызывает одну из пакетных процедур. Одна переменная пакета является таблицей для удаленных EMPNO, а другая - индексом для нее. Триггер BEFORE DELETE уровня оператора просто вызывает процедуру, которая устанавливает этот индекс в нуль, т.е. логически очищает таблицу. Триггер BEFORE DELETE уровня строки передает первичный ключ (EMPNO) удаляемой записи, и он добавляется в конец таблицы. Триггер AFTER DELETE уровня оператора проходит по таблице и обнуляет столбец MGR для всех служащих, у которых удаленный служащий был начальником.

Примечание

В примере Б.1 используются вызовы пакетных процедур внутри триггера, чтобы глобальные переменные пакета могли оставаться локальными для процедуры. Этот же прием можно реализовать с меньшими затратами, для чего нужно просто поместить глобальные переменные пакета в сам пакет (а не в тело пакета) и манипулировать ими прямо из триггеров. Как вы, наверное, догадываетесь (если прочитали остальные главы книги), мы предпочитаем исключительно инкапсулированный подход.

Пример Б. 1. Как избежать появления мутирующих таблиц путем откладывания действия до уровня операторов

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,-