Иван Задворьев - Язык PL/SQL

14 /

Trigger created.

SQL> INSERT INTO tab1 VALUES(4);

1 row created.

SQL> INSERT INTO tab1 VALUES(7);

INSERT INTO tab1 VALUES(7)

*

ERROR at line 1:

ORA-20002: Слишком большое уклонение

ORA-06512: at "U1.TRIG_TB1", line 9

ORA-04088: error during execution of trigger 'U1.TRIG_TB1'

SQL> SELECT * FROM tab1;

AT1

–

1

3

5

4

При добавлении значения 4, достаточно близкого к среднему, исключение в триггере не инициируется. При добавлении значения 7, определяется большое уклонение от среднего, инициируется исключение и новая строка в таблицу не добавляется.

Если код триггера содержит ошибки, то он все равно будет создан, но выполнение предложений SQL, на которые он должен срабатывать, будет завершаться ошибкой. Такие триггеры следует или удалить, или исправить, или временно отключить командой ALTER TRIGGER … DISABLE.

Использование триггеров с различными настройками

Возможные значения трех настроек дают 12 вариантов событий для срабатывания триггеров для выполнения DML-предложений:

12=2 (BEFORE/AFTER) * 2 (уровня строки / предложения) * 3 (INS/UPD/DEL)

Триггеры уровня предложения SQL часто используются для реализации правил, определяющих возможность выполнения предложения SQL. Например, пусть в некоторой организации нельзя оформлять пропуска посетителям в нерабочее время. Это требования может быть реализовано BEFORE-триггером для предложения INSERT, «навешенным» на таблицу пропусков. Внутри этого триггера надо проверять, что текущее время находится в заданном интервале рабочих часов 09:00-18:00, а текущий день не является выходным. Если эта проверка не выполняется, то в триггере инициируется исключение. Если в BEFORE-триггере инициируется исключение, то до добавления записей посредством предложения INSERT в таблицу пропусков дело не дойдет, что и требуется.

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

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