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

значения атрибутов :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 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: