Иван Задворьев - Язык PL/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. Перед перезапуском выполняется отмена до неявно установленной точки сохранения, в ходе которой в том числе отменяются изменения, сделанные сработавшими до перезапуска триггерами уровня строки. Затем в ходе повторной обработки строк эти триггеры срабатывают снова. Может случиться так, что эти строки окажутся другими, не теми, которые пытались обработать в первый раз. Чаще же происходят ситуации, когда триггеры срабатывают на одних и тех же строках и при первой (отмененной) обработке строк, и в ходе перезапуска. Таким образом, на одной строке один и тот же триггер уровня строки может сработать дважды.

Если в триггере не используются автономные транзакции, то ничего страшного в его повторных срабатываниях нет – сделанные во время первых срабатываний изменения будут отменены при автоматической отмене к неявной точке сохранения перед перезапуском. Зафиксированы будут только изменения, сделанные во время вторых срабатываний триггеров.

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

Дополнительное условие срабатывания триггера

Срабатывание триггеров может существенно замедлить выполнение предложений 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%.