Рассмотрим, какие типы триггеров целесообразно использовать для решения двух типовых задач с учетом имеющихся ограничений на работу с псевдозаписями :NEW и :OLD:
для модификации (подмены) значений строк с помощью :NEW следует использовать BEFORE-триггер уровня строки (потому что изменения в атрибутах :NEW возможны только в BEFORE-триггерах);
для проверки (validation) новых значений столбцов обрабатываемой строки следует использовать AFTER-триггер уровня строки.
Вообще говоря, проверки новых данных можно делать и в BEFORE-триггере (:NEW в таких триггерах доступна и на чтение и на запись), однако так делать можно только в том случае, когда BEFORE-триггер один и в нем осуществляется и подмена значений столбцов и их проверка. Порядок срабатывания триггеров одного типа в Oracle до недавнего времени был не определен. Поэтому если триггеров уровня строки на одно событие несколько, то триггер, подменяющий значения столбцов, может сработать и после триггера с проверкой, выставив некорректное с точкой зрения проверки значения (проверка окажется преждевременной). Такой ситуации не возникнет для AFTER-триггеров, которые «видят» псевдозапись :NEW, которая теперь точно никак уже не изменится (изменения строки уже внесены в блоки данных таблицы предложением SQL и поменять строку там еще раз ни в одном AFTER-триггере невозможно). Именно окончательную версию :NEW следует проверить на корректность в AFTER-триггере.
Таким образом, общее правило для триггеров уровня строки такое: «подменяем значения столбцов обрабатываемых строк на новые в BEFORE-триггерах, проверяем новые значения в AFTER-триггерах».
Если триггер реализует реакцию на совершение какого-либо события, то выполнять его правильно после предложения SQL, относящегося к этому событию. Например, если требуется обновлять баланс по итогам добавления нового платежа, то следует делать это AFTER-триггером уже после успешного добавления строки в таблицу платежей, так как баланс логично обновлять только после того, как успешно прошел платеж.
Триггеры в транзакциях
Выполняемые в коде триггера предложения SQL являются частью транзакции, в которую входит предложение SQL, вызвавшее срабатывание триггера. Все предложения SQL в коде триггера выполняются на том же «срезе» базы данных, что и вызвавшее срабатывание триггера предложение SQL. Это распространяется на изменения, внесенные другими транзакциями, их в теле триггера не видно. Если же в ходе выполнения одного предложения SQL происходит несколько срабатываний триггеров, то предложения SQL каждого сработавшего триггера видят изменения, сделанные на предыдущих срабатываниях. Все как всегдачужие изменения на уровне отдельного предложения SQL не видны и в транзакции всегда видны свои изменения.
Отметим следующие важные обстоятельства:
если в триггере будет инициировано необработанное исключение, то вызвавшее срабатывание триггера предложение SQL завершится с ошибкой и будет выполнена отмена и всех изменений, сделанных предложением SQL, и всех изменений, сделанных всеми триггерами на него (в ходе отмены до неявно установленной точки сохранения перед предложением);
в триггере нельзя выполнять команды фиксации и отмены транзакций COMMIT и ROLLBACK (написать в теле триггера команды COMMIT или ROLLBACK можнотриггер будет успешно создан, но ошибка возникнет на этапе выполнения).
В примере с запретом выдачи пропусков в нерабочее время следует использовать BEFORE-триггер уровня предложения. Отмена изменений происходить не будет, так как не будет самих изменений данных исключение в триггере будет инициировано еще до выполнения INSERT в таблицу пропусков. Если же в примере с обновлением триггером баланса после поступления платежа произойдет необработанная ошибка в триггере, то сам платеж, на добавление которого сработал триггер, тоже будет отмененбудет отменено добавление строки в таблицу платежей (новая строка платежа «исчезнет»).
Транзакция после ошибки в триггере остается в активном статусе, то есть сама по себе не отменяется и не фиксируется. Просто завершилось с ошибкой одно из входивших в нее предложений SQL, всю транзакцию потом можно будет зафиксировать или отменить. Понятно, что если фиксируется или отменяется транзакция, то это относится и ко всем изменениям, сделанным триггерами, срабатывавшими на предложениях SQL транзакции.
При наличии BEFORE-триггера к строке происходит три обращения (на примере UPDATE):
в режиме согласованного чтения строка отбирается предложением UPDATE для изменения (первое обращение);
выполняется блокирование строки командой SELECT FOR UPDATE (второе обращение);
срабатывает BEFORE-триггер и значения столбцов заблокированной строки передаются в его псевдозапись :OLD;
в ходе изменения строки предложением UPDATE происходит третье обращение к строке в текущем состоянии.
Наличие AFTER-триггеров не приводит к дополнительным обращениям к строке. Блокирования строки не происходит, после отбора строки в режиме согласованного чтения и ее изменения в текущем состоянии срабатывает AFTER-триггер, которому передаются данные для заполнения псевдозаписей :NEW и :OLD. Как отмечалось выше, изменить значения столбцов строки он уже не сможет.
Последовательность срабатывания триггеров
Пусть, например, на некоторую таблицу «навешено» все 2*2=4 триггера со срабатыванием на предложение UPDATE:
BEFORE-триггер уровня предложения SQL tr1;
BEFORE-триггер уровня строки tr2;
AFTER-триггер уровня строки tr3;
AFTER-триггер уровня предложения SQL tr4.
Последовательность событий при выполнении предложения UPDATE, которое изменит, скажем, две строки в таблице, будет следующая:
один раз сработает триггер tr1;
на первой изменяемой строке сработает триггер tr2;
выполнится изменение первой строки предложением UPDATE;
на первой измененной строке сработает триггер tr3;
на второй изменяемой строке сработает триггер tr2;
выполнится изменение второй строки предложением UPDATE;
на второй измененной строке сработает триггер tr3;
один раз сработает триггер tr4.
Проверим возможность изменять значения атрибуты псевдозаписи :NEW, заодно и проиллюстрируем приведенную выше последовательность срабатывания триггеров:
CREATE TABLE tab5 (at1 INTEGER); INSERT INTO tab5 VALUES(5);
CREATE OR REPLACE TRIGGER before_statement BEFORE UPDATE ON tab5
BEGIN
dbms_lock.sleep(2);
DBMS_OUTPUT.PUT_LINE('Fire before statement-level trigger at '
||TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS'));
END;
CREATE OR REPLACE TRIGGER before_row BEFORE UPDATE ON tab5 FOR EACH ROW
BEGIN
dbms_lock.sleep(2);
DBMS_OUTPUT.PUT_LINE('Fire before row-level trigger at '
||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);
:NEW.at1 := 6;
DBMS_OUTPUT.PUT_LINE('Set :NEW.at1='||:NEW.at1);
DBMS_OUTPUT.PUT_LINE('Finish before row-level trigger');
END;
CREATE OR REPLACE TRIGGER after_statement AFTER UPDATE ON tab5
BEGIN
dbms_lock.sleep(2);
DBMS_OUTPUT.PUT_LINE('Fire after statement-level trigger at '
||TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS'));
END;
CREATE OR REPLACE TRIGGER after_row AFTER UPDATE ON tab5 FOR EACH ROW
BEGIN
dbms_lock.sleep(2);
DBMS_OUTPUT.PUT_LINE('Fire after row-level trigger at '
||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. Перед перезапуском выполняется отмена до неявно установленной точки сохранения, в ходе которой в том числе отменяются изменения, сделанные сработавшими до перезапуска триггерами уровня строки. Затем в ходе повторной обработки строк эти триггеры срабатывают снова. Может случиться так, что эти строки окажутся другими, не теми, которые пытались обработать в первый раз. Чаще же происходят ситуации, когда триггеры срабатывают на одних и тех же строках и при первой (отмененной) обработке строк, и в ходе перезапуска. Таким образом, на одной строке один и тот же триггер уровня строки может сработать дважды.