Триггеры на выполнение DML-предложений
Каждый триггер на выполнение предложений INSERT, UPDATE, DELETE «навешивается» на одну конкретную таблицу и имеет три основные настройки:
набор предложений SQL INSERT, UPDATE, DELETE, при выполнении которых будет срабатывать триггер;
тип срабатываниядо (BEFORE) или после (AFTER) внесения изменений в данные в ходе выполнения предложения SQL, вызвавшего срабатывание триггера;
сколько раз триггер будет срабатыватьодин раз или по числу обработанных предложением SQL строк.
Рассмотрим эти настройки подробнее.
Для одного триггера можно указать любую непустую комбинацию из трех предложений INSERT, DELETE, UPDATE (всего получается 23-1=7 комбинаций). Если эта комбинация включает предложение UPDATE, то могут быть указаны конкретные столбцы таблицы, значения которых должны изменяться предложениями UPDATE, чтобы вызвать срабатывание триггера.
По количеству срабатываний триггеры делятся на два вида:
триггеры уровня предложения (statement-level triggers)срабатывают один раз при выполнении вызвавшего срабатывание предложения SQL;
триггеры уровня строки (row-level triggers)срабатывают на каждой строке, обрабатываемой вызвавшим срабатывание триггера предложением SQL.
Триггер уровня предложения при выполнении в базе данных предложения SQL, на которое он настроен, срабатывает всегда и срабатывает ровно один раз. А вот триггер уровня строки может не сработать ни разу, если предложение SQL не обработало ни одной строки. Если же предложение SQL обработало три строки, то триггер уровня строки сработает три раза, обработка десяти строк вызовет десять срабатываний такого триггера и так далее.
Условие срабатывания триггера уровня строки может быть уточнено дополнительным логическим условием в конструкции WHEN команды CREATE TRIGGER.
Команда создания триггера на выполнение DML-предложений имеет следующий синтаксис:
CREATE [OR REPLACE] TRIGGER имя_триггера
{BEFORE | AFTER}тип срабатывания
{ INSERT | DELETE | UPDATE | UPDATE OF список столбцов } ON имя таблицы
[FOR EACH ROW]триггер уровня строки
[WHEN ()]дополнительное логическое условие срабатывания
остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)
END;
В коде триггеров можно использовать специфичные средства:
операционные директивы INSERTING, UPDATING, DELETING;
псевдозаписи :NEW и :OLD (только для триггеров уровня строки).
Операционные директивы
Операционные директивы INSERTING, UPDATING, DELETING предназначены для идентификации предложения SQL, вызвавшего срабатывание триггера. Так как при создании триггера может указываться любая непустая комбинация из трех предложений INSERT, UPDATE, DELETE, то с помощью операционных директив INSERTING, UPDATING, DELETING внутри блока PL/SQL можно реализовать отдельные ветви потока команд для каждого из этих предложений.
Пусть, например, триггер срабатывает на INSERT и на DELETE, тогда исполняемый раздел блока триггера может быть построен следующим образом:
CASE
WHEN INSERTING THEN
логика обработки при срабатывании на INSERT
WHEN DELETING THEN
логика обработки при срабатывании на DELETE
END CASE;
Псевдозаписи :NEW и :OLD
Интуитивно ясно, что внутри триггеров уровня строки должна быть возможность обращаться к значениям столбцов строк, на которых срабатывают триггеры этого вида.
При каждом запуске триггера уровня строки виртуальная машина PL/SQL создает и заполняет две структуры данныхпсевдозаписи :NEW и :OLD. Их структура идентична структуре записи PL/SQL, объявленной с помощью атрибута %ROWTYPE, то есть псевдозапись имеет все атрибуты с такими же именами и типами данных, какие есть столбцы у таблицы, на которую «навешен» триггер. В атрибутах псевдозаписи :OLD находятся исходные значения столбцов строки, на которой сработал триггер, а в атрибутах псевдозаписи :NEWновые значения столбцов.
Перечислим понятные ограничения, касающиеся этих псевдозаписей:
у триггеров для INSERT нет данных в атрибутах :OLD;
у триггеров для DELETE нет данных в атрибутах :NEW и изменять их нельзя;
значения атрибутов :OLD изменять нельзя;
значения атрибутов :NEW можно изменять в BEFORE-триггерах.
Полностью сведения о значениях атрибутов псевдозаписей :NEW и :OLD приведены в следующей таблице.
Таблица 6. Псевдозаписи :NEW и :OLD.
SQL
:OLD
:NEW
INSERT
NULL
значения столбцов после добавления
UPDATE
значения до изменения
значения столбцов после изменения
DELETE
значения перед удалением
NULL
Если для таблицы имеется несколько BEFORE-триггеров, то в ходе срабатываний друг за другом они могут несколько раз изменять значения псевдозаписи :NEW и каждый срабатывающий триггер будет видеть текущее ее состояниепосле последнего изменения.
То обстоятельство, что в триггерах уровня строки со срабатыванием на INSERT и UPDATE значения атрибутов псевдозаписи :NEW можно изменять, позволяет подменять в триггере новые значения столбцов обрабатываемых этими предложениями SQL строк. Иными словами, если какое-нибудь предложение UPDATE делало в таблице из семерок восьмерки, то в конечном итоге в базе могут оказаться девятки, подмена на которые была выполнена в BEFORE-триггере. Как отмечалось ранее, наличие таких неожиданных эффектов при выполнении предложений SQLэто одна из причин считать использование триггеров плохой практикой.
Пример использования триггера
Пусть таблица tab1 создана и заполнена следующим образом:
CREATE TABLE tab1 (at1 NUMBER);
INSERT INTO tab1 VALUES(1);
INSERT INTO tab1 VALUES(3);
INSERT INTO tab1 VALUES(5);
Создадим триггер, который выдает ошибку, если значение столбца добавляемой строки слишком уклоняется от среднего значения для текущего состояния таблицы. В роли меры слишком большого уклонения выберем широко применяемое в инженерной практике правило «трех сигм»:
SQL> CREATE OR REPLACE TRIGGER trig_tb1
2 BEFORE INSERT ON tab1 FOR EACH ROW
3 DECLARE
4 stat_avg NUMBER;
5 stat_std NUMBER;
6 stat_n NUMBER;
7 BEGIN
8 SELECT COUNT(at1),SUM(at1),STDDEV(at1)
9 INTO stat_n,stat_avg,stat_std FROM tab1;
10 IF (ABS(stat_avg-stat_n*(:NEW.at1))/(SQRT(stat_n)*stat_std)>3) THEN
11 RAISE_APPLICATION_ERROR(-20002, 'Слишком большое уклонение');
12 END IF;
13 END;
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 TRIGGERDISABLE.
Использование триггеров с различными настройками
Возможные значения трех настроек дают 12 вариантов событий для срабатывания триггеров для выполнения DML-предложений:
12=2 (BEFORE/AFTER) * 2 (уровня строки / предложения) * 3 (INS/UPD/DEL)
Триггеры уровня предложения SQL часто используются для реализации правил, определяющих возможность выполнения предложения SQL. Например, пусть в некоторой организации нельзя оформлять пропуска посетителям в нерабочее время. Это требования может быть реализовано BEFORE-триггером для предложения INSERT, «навешенным» на таблицу пропусков. Внутри этого триггера надо проверять, что текущее время находится в заданном интервале рабочих часов 09:00-18:00, а текущий день не является выходным. Если эта проверка не выполняется, то в триггере инициируется исключение. Если в BEFORE-триггере инициируется исключение, то до добавления записей посредством предложения INSERT в таблицу пропусков дело не дойдет, что и требуется.
Триггеры уровня строки обычно используются для реализации собственно бизнес-логики. Можно считать, что каждая добавленная, удаленная или измененная строка в таблицеэто отдельное событие, которое требует своей обработки. Например, если предложение DELETE удаляет из таблицы платежей несколько ошибочно добавленных в нее строк, то требуется по каждому удаленному платежу изменить (уменьшить) баланс лицевого счета, на который в свое время поступил этот платеж. Понятно, что код, осуществляющий это действие, должен выполняться для каждой удаленной строки, то есть в триггере уровня строки.