Существует известная техника решения проблемы мутирующей таблицы с условным наименованием «один пакет и три триггера»:
создать 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-адрес клиента, название клиентского приложения), или устанавливаться переменные окружения сессии пользователя. В триггере на завершение сессии может собираться статистика о выполненных в ходе этой сессии операциях.
Триггеры на события базы данных обычно создаются самими администраторами баз данных или самыми опытными разработчиками прикладных систем.