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

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

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

Рассуждения строятся следующим образом. Запрет введен для недопущения неоднозначности результатов обращений из триггера к мутирующей таблице из-за отсутствия порядка обработки строк. Понятно, что этой неоднозначности не будет, если предложение 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). Динамические ограничения целостности являются более сложными – не декларируемыми, а программируемыми. Рассмотрим пример такого ограничения.

Пусть в базе данных хранятся сведения о договорах клиентов и их лицевых счетах. Отношение между договорами и счетами – «один ко многим», то есть для одного договора есть несколько лицевых счетов.

CREATE TABLE contracts

(id INTEGER PRIMARY KEY,

num VARCHAR2(10),

status VARCHAR2(10));

CREATE TABLE accounts

(id INTEGER,

num VARCHAR2(10),

r$contract$id INTEGER REFERENCES contracts,

status VARCHAR2(10));

INSERT INTO contracts VALUES(12,'562/323-21','operating');

INSERT INTO accounts VALUES(45,'321/21-1',12,'operating');

INSERT INTO accounts VALUES(46,'321/21-2',12,'closed');

Пусть имеется динамическое ограничение целостности – запрет закрытия контракта клиента до тех пор, пока не закрыты все его лицевые счета. Такое ограничение целостности можно реализовать BEFORE-триггером уровня строки.