Иван Сергеевич Задворьев
Язык PL/SQL
||TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE(':OLD.at1='||:OLD.at1);
DBMS_OUTPUT.PUT_LINE(':NEW.at1='||:NEW.at1);
DBMS_OUTPUT.PUT_LINE('Finish after row-level trigger');
END;
SQL> UPDATE tab5 SET at1=10;
Fire before statement-level trigger at 18.01.2015 12:00:05
Fire before row-level trigger at 18.01.2015 12:00:07
:OLD.at1=5
:NEW.at1=10
Set :NEW.at1=6
Finish before row-level trigger
Fire after row-level trigger at 18.01.2015 12:00:09
:OLD.at1=5
:NEW.at1=6
Finish after row-level trigger
Fire after statement-level trigger at 18.01.2015 12:00:11
1 row updated.
SQL> select * from tab5;
AT1
–
6
Меняли предложением UPDATE пятерку на десятку, в итоге в базе шестерка. Налицо неожиданный побочный эффект, по этой причине триггеры и не рекомендуют использовать.
У сервера Oracle для обеспечения согласованности изменений данных при необходимости осуществляется автоматический перезапуск предложений UPDATE и DELETE. Перед перезапуском выполняется отмена до неявно установленной точки сохранения, в ходе которой в том числе отменяются изменения, сделанные сработавшими до перезапуска триггерами уровня строки. Затем в ходе повторной обработки строк эти триггеры срабатывают снова. Может случиться так, что эти строки окажутся другими, не теми, которые пытались обработать в первый раз. Чаще же происходят ситуации, когда триггеры срабатывают на одних и тех же строках и при первой (отмененной) обработке строк, и в ходе перезапуска. Таким образом, на одной строке один и тот же триггер уровня строки может сработать дважды.
Если в триггере не используются автономные транзакции, то ничего страшного в его повторных срабатываниях нет – сделанные во время первых срабатываний изменения будут отменены при автоматической отмене к неявной точке сохранения перед перезапуском. Зафиксированы будут только изменения, сделанные во время вторых срабатываний триггеров.
Использовать в триггерах автономные транзакции рекомендуется только для регистрации ошибок в журналах. Реализация бизнес-логики с помощью автономных транзакций, как правило, содержит ошибки обеспечения корректного многопользовательского доступа, которые обязательно произойдут рано или поздно.
Дополнительное условие срабатывания триггера
Срабатывание триггеров может существенно замедлить выполнение предложений SQL, особенно когда обрабатывается много строк и на каждой из них срабатывает триггер уровня строки. Этот триггер в соответствии с бизнес-логикой может для каких-то ситуаций не выполнять никаких операций с данными, но все равно его срабатывание на каждой строке будет ухудшать производительность.
В заголовке триггера после необязательного ключевого слова WHEN можно задать дополнительное логическое условие, сужающее область событий, при наступлении которых триггер запускается. Это очень ценная возможность, которая позволяет сделать так, чтобы какие-то строки обрабатывались без срабатывания триггера.
Рассмотрим соответствующий пример. Пусть имеется таблица платежей
CREATE TABLE payments (pay_date DATE,account INTEGER,
amount INTEGER, source VARCHAR2(20));
Платежи поступают сотнями тысяч и бывают двух типов – через кассы и через сайт. Для платежей, поступивших через сайт, требует проводить дополнительную обработку, для платежей через кассу этого не требуется.
CREATE OR REPLACE TRIGGER tr$payments$b$i
BEFORE INSERT ON payments FOR EACH ROW WHEN (NEW.source = 'online')
BEGIN
dbms_output.put_line('Триггер сработал');
– process_onine_payment(:NEW.account,:NEW.amount);
END;
SQL> INSERT INTO payments VALUES(SYSDATE,3452,1000,'online');
Триггер сработал
1 row(s) inserted
SQL> INSERT INTO payments VALUES(SYSDATE,7854,500,'cashbox');
1 row(s) inserted
Видно, что во втором случае срабатывания триггера не было. Для этой же цели минимизации ненужного использования ресурсов сервера триггерами предназначена и возможность их временного отключения DDL-командой ALTER:
SQL> ALTER TRIGGER trig_tb1 DISABLE;
Trigger altered.
SQL> ALTER TRIGGER trig_tb1 ENABLE;
Trigger altered.
Для триггеров, срабатывающих при выполнении предложений UPDATE, также можно указать в конструкции UPDATE OF список столбцов, которые должны изменяться для того, чтобы триггер сработал. Все условия в заголовке и в конструкции WHEN проверяются без запуска триггера во время выполнения предложения SQL. По этой причине в конструкции WHEN можно использовать только встроенные функции SQL.
Чтобы подчеркнуть важность рассмотренного вопроса минимизации числа ненужного срабатываний триггеров, отметим, что по некоторым оценкам замедление выполнения DML-предложений из-за наличия одного триггера может составить до 30%.
Мутирующие таблицы
Мутирующая таблица (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). Динамические ограничения целостности являются более сложными – не декларируемыми, а программируемыми. Рассмотрим пример такого ограничения.
Пусть в базе данных хранятся сведения о договорах клиентов и их лицевых счетах. Отношение между договорами и счетами – «один ко многим», то есть для одного договора есть несколько лицевых счетов.
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-адрес клиента, название клиентского приложения), или устанавливаться переменные окружения сессии пользователя. В триггере на завершение сессии может собираться статистика о выполненных в ходе этой сессии операциях.
Триггеры на события базы данных обычно создаются самими администраторами баз данных или самыми опытными разработчиками прикладных систем.