Иван Задворьев - Язык PL/SQL

Мутирующие таблицы

Мутирующая таблица (mutating table) – это таблица, строки которой в данный момент изменяются предложением SQL. Таблицы, строки в которых которые изменяются в результате ссылочных действий (ON DELETE CASCADE, ON DELETE SET NULL), также являются изменяющимися.

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

Приведем пример мутирующей таблицы:

CREATE TABLE tab1 (at1 INTEGER,at2 INTEGER);

INSERT INTO tab1 VALUES(1,1);

INSERT INTO tab1 VALUES(2,1);

SQL> CREATE OR REPLACE TRIGGER tr1

2 BEFORE DELETE ON tab1 FOR EACH ROW

3 BEGIN

4 IF :OLD.at1=:OLD.at2 THEN

5 UPDATE tab1 SET at2=NULL

6 WHERE at2=:OLD.at1;

7 END IF;

8 END;

9 /

Trigger created.

SQL> DELETE FROM tab1 WHERE at1=at2;

DELETE FROM tab1 WHERE at1=at2

*

ERROR at line 1:

ORA-04091: table U1.TAB1 is mutating, trigger/function may not see it

ORA-06512: at "U1.TR1", line 2

ORA-04088: error during execution of trigger 'U1.TR1'

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

Пусть таблица tab1 имеет один столбец at1 и пять строк:

CREATE TABLE tab1 (at1 INTEGER)

SQL> SELECT * FROM tab1;

AT1

–

1

2

3 – смотрим срабатывание триггера на этой строке

0

4

Выполняем предложение

UPDATE tab2 SET at1=at1+1;

Пусть на каждой обрабатываемой строке срабатывает AFTER-триггер уровня строки, в коде которого выполняется запрос

SELECT COUNT(*) FROM tab2 WHERE at1<3

Смотрим результаты этого SQL-запроса для строки с тройкой при двух разных вариантах порядка обработки строк (o,old – старое значение, n,new – новое значение):

Первый вариант порядка обработки строк

Второй вариант порядка обработки строк

|

o1-> n2

o3-> n4 COUNT:return 3:row(n2,o2,o0)

o2-> o2 (пока не менялось)

o0-> o0 (пока не менялось)

o4-> o4 (пока не менялось)

|

o1-> n2

o4-> n5

o0-> n1

o2-> n3

o3-> n4 COUNT:return 2:row(n2,n1)

Для первого варианта порядка обработки строк (1,3,2,0,4) SQL-запрос в теле триггера возвращает число 3 для COUNT(*), для второго варианта (1,4,0,2,3) – число 2 (3<>2). То есть один и тот же запрос при одинаковом исходном содержимом таблицы в ходе срабатывания триггера на одной и той же строке может вернуть различные результаты.

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

Запрет доступа к мутирующим таблицам относится только к триггерам уровня строки. Триггеры уровня предложения SQL могут и считывать, и записывать данные мутирующей таблицы. Это понятно – перед триггером уровня предложения «лежит» множество всех строк, обрабатываемых предложением SQL. Для AFTER-триггера они все уже обработаны, для BEFORE-триггера они все еще не обработаны. В таких условиях действия с данными в мутирующей таблице в триггере при любом исходном порядке строк в таблице будут завершаться с одинаковыми результатами.

Исключение из запрета доступа к мутирующим таблицам

У запрета доступа к мутирующим таблицам из триггеров уровня строки есть исключение.

Рассуждения строятся следующим образом. Запрет введен для недопущения неоднозначности результатов обращений из триггера к мутирующей таблице из-за отсутствия порядка обработки строк. Понятно, что этой неоднозначности не будет, если предложение SQL обрабатывает ровно одну строку – в этом вырожденном случае обработка строк, очевидно, упорядочена. Таким предложением SQL является предложение INSERT.

Для предложений UPDATE и DELETE понять, сколько строк они обработают, находясь на первой из обработанных им строк, нельзя. Неясно, будет ли после этой строки потом обработана еще вторая, третья и последующие строки. В то же время сам синтаксис предложения INSERT предусматривает, что оно добавляет в таблицу ровно одну строку, поэтому в BEFORE-триггерах уровня строки для таких предложений INSERT можно обращаться к мутирующей таблице:

SQL> CREATE TABLE tab3 (at1 INTEGER);

Table created.

SQL> CREATE OR REPLACE TRIGGER tr$tab3$i

2 BEFORE INSERT ON tab3 FOR EACH ROW

3 DECLARE

4 l_count INTEGER;

5 BEGIN

6 SELECT count(*) INTO l_count FROM tab3;

7 END;

8 /

Trigger created.

SQL> INSERT INTO tab3 VALUES (1);

1 row created.

В то же время, если выполнить специальную форму предложения INSERT – INSERT SELECT, которая потенциально может добавить в таблицу не одну, а несколько строк, произойдет ошибка.

SQL> INSERT INTO tab3 SELECT * FROM tab3;

INSERT INTO tab3 SELECT * FROM tab3

*

ERROR at line 1:

ORA-04091: table U1.TAB3 is mutating, trigger/function may not see it

ORA-06512: at "U1.TR$TAB3$I", line 4

ORA-04088: error during execution of trigger 'U1.TR$TAB3$I'

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

Решения проблемы мутирующей таблицы

Для решения проблемы мутирующей таблицы применяются три основных способа:

использование в триггерах автономных транзакций;

использование составных триггеров (compound triggers);

перенос логики триггеров уровня строки в триггеры уровня предложения SQL.

Существует известная техника решения проблемы мутирующей таблицы с условным наименованием «один пакет и три триггера»:

создать BEFORE-триггер уровня предложения, который обнуляет «индекс» таблицы PL/SQL, объявленной как глобальная переменная в спецификации пакета;

создать BEFORE-триггер уровня строки, который для каждой обработанной предложением SQL строки запоминает требуемые значения в записи таблицы PL/SQL;

создать AFTER-триггер уровня предложения, выполняющий требуемые изменения по значениям, запомненным в таблице PL/SQL.

Авторы хотят предостеречь читателя от применения подобных способов, особенно автономных транзакций (autonomous transactions in triggers are pure evil). Они работоспособны только в условиях однопользовательской обработки. Возникновение проблемы мутирующей таблицы, если ее не удалось решить изменением логики в коде самого триггера, следует рассматривать как повод для решения вовсе отказаться от триггера в этом случае и переработать логику обработки данных без него.

Реализация динамических ограничений целостности

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

Динамическим ограничение целостности (dynamic integrity constraint) называется динамически проверяемое ограничение, определяющее возможность перехода моделируемой предметной области из одного состояния в другое состояние. Это такие ограничения, которые невозможно реализовать в виде статических ограничений целостности для таблиц (первичных и внешних ключей, ограничений на уникальность и ограничений целостности, задаваемых предикатом CHECK). Динамические ограничения целостности являются более сложными – не декларируемыми, а программируемыми. Рассмотрим пример такого ограничения.