Язык PL/SQL

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

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

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-триггером уровня строки.

SQL> CREATE OR REPLACE TRIGGER tr$contracts$u

2 BEFORE UPDATE ON contracts FOR EACH ROW WHEN (NEW.status = 'closed')

3 DECLARE

4 l_account_count INTEGER;

5 BEGIN

6

7 SELECT count(*) INTO l_account_count

8 FROM accounts WHERE accounts.r$contract$id = :NEW.id

9 AND accounts.status <> 'closed';

10

11 IF l_account_count > 0 THEN

12 RAISE_APPLICATION_ERROR(-20001,

13 'У контракта '||:NEW.id||' имеются незакрытые лицевые счета');

14 END IF;

15

16 END;

17 /

Trigger created.

SQL> UPDATE contracts SET status='closed' WHERE contracts.id=12;

UPDATE contracts SET status='closed' WHERE contracts.id=12

*

ERROR at line 1:

ORA-20001: У контракта 12 имеются незакрытые лицевые счета

ORA-06512: at "U1.TR$CONTRACTS$U", line 10

ORA-04088: error during execution of trigger 'U1.TR$CONTRACTS$U'

– закрываем лицевые счет 12-го контракта

SQL> UPDATE accounts SET status='closed' WHERE r$contract$id=12;

2 rows updated.

– теперь закрыть контракт можно

SQL> UPDATE contracts SET status='closed' WHERE contracts.id=12;

1 row updated.

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

Триггеры на создание, изменение и удаление объектов базы данных

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

Команда создания триггера на создание, изменение и удаление объектов базы данных имеет следующий синтаксис:

CREATE [OR REPLACE] TRIGGER имя триггера

{BEFORE | AFTER} – тип срабатывания

{событие с объектом базы данных } ON {база данных | схема}

[WHEN (…)] – дополнительное логическое условие

остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)

Под событиями с объектами базы данных понимается выполнение команд из фиксированного перечня: CREATE, ALTER, DROP, GRANT, REVOKE, TRUNCATE TABLE и некоторые другие.

Для получения в триггерах информации об объектах баз данных и о типах происходящих с ними событий, предназначены атрибутные функции.

В версии Oracle 12с имеется 20 атрибутных функций, приведем описание некоторых из них.

Таблица 7. Атрибутные функции.

Атрибутная функция

Описание функции

ORA_CLIENT_IP_ADDRESS

IP-адрес клиента

ORA_DICT_OBJ_NAME

имя объекта базы данных, связанного с DDL-командой, которая вызвала срабатывание триггера

ORA_DICT_OBJ_OWNER

владелец объекта, связанного с DDL-командой, которая вызвала срабатывание триггера

ORA_DICT_OBJ_TYPE

тип объекта, связанного с DDL-командой, которая вызвала срабатывание триггера

ORA_SYSEVENT

тип события, вызвавшего срабатывание триггера (например, CREATE, DROP или ALTER)

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

запретим удаление любых таблиц (триггер tr$drop_table$disable);

разрешим назначение привилегий только при подключениях к серверу баз данных сети с конкретного IP-адреса в локальной сети (триггер tr$check_grantee_ip).

Такого вида триггеры могут создаваться администраторами баз данных (администраторами безопасности) для повышения степени контроля за системой:

«чтобы никто ни одной таблички не смог удалить ни при каких обстоятельствах – только я, причем несколько раз подумав и предварительно отключив триггер»;

«назначать привилегии можно было только с моей рабочей станции».

SQL> CREATE OR REPLACE TRIGGER tr$drop_table$disable

2 BEFORE DROP ON DATABASE

3 BEGIN

4 IF ORA_SYSEVENT = 'DROP'

5 AND ORA_DICT_OBJ_TYPE = 'TABLE' THEN

6 RAISE_APPLICATION_ERROR (

7 -20000,

8 'ERROR : Tables cannot be dropped in my database!');

9 END IF;

10 END;

11 /

Trigger created.

SQL> DROP TABLE tab1;

DROP TABLE tab1

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20000: ERROR : Tables cannot be dropped in my database!

ORA-06512: at line 4

SQL> CREATE OR REPLACE TRIGGER tr$check_grantee_ip

2 BEFORE GRANT ON DATABASE

3 DECLARE

4 c_valid_ip CONSTANT VARCHAR2(20) := '192.168.0.8';

5 l_current_ip VARCHAR2(20);

6 BEGIN

7 l_current_client_ip := sys_context('USERENV','IP_ADDRESS');

8 IF ORA_SYSEVENT = 'GRANT'

9 AND l_current_client_ip <> c_valid_ip THEN

10 RAISE_APPLICATION_ERROR (

11 -20000,

12 'ERROR: Grants from '||l_current_ip||' not allowed');

13 END IF;

14 END;

15 /

Trigger created.

SQL> GRANT SELECT ON tab1 TO u1;

GRANT SELECT ON tab1 TO u1

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20000: ERROR: Grants from 127.0.0.1 not allowed

ORA-06512: at line 8

Триггеры на события базы данных

Триггеры на события базы данных запускаются при возникновении событий уровня базы данных. В Oracle 12c восемь таких типов событий, перечислим некоторые из них:

STARTUP – открытие базы данных;

SHUTDOWN – нормальное закрытие базы данных;

SERVERERROR – возникновение ошибки;

LOGON – создание сеанса;

LOGOFF – нормальное завершение сеанса.

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

Триггеры на события базы данных обычно создаются самими администраторами баз данных или самыми опытными разработчиками прикладных систем.