Иван Задворьев - Язык PL/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%.

Мутирующие таблицы

Мутирующая таблица (mutating table) – это таблица, строки которой в данный момент изменяются предложением SQL. Таблицы, строки в которых которые изменяются в результате ссылочных действий (ON DELETE CASCADE, ON DELETE SET NULL), также являются изменяющимися.

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

Приведем пример мутирующей таблицы:

CREATE TABLE tab1 (at1 INTEGER,at2 INTEGER);

INSERT INTO tab1 VALUES(1,1);

INSERT INTO tab1 VALUES(2,1);

SQL> CREATE OR REPLACE TRIGGER tr1

2 BEFORE DELETE ON tab1 FOR EACH ROW

3 BEGIN

4 IF :OLD.at1=:OLD.at2 THEN

5 UPDATE tab1 SET at2=NULL

6 WHERE at2=:OLD.at1;

7 END IF;

8 END;

9 /

Trigger created.

SQL> DELETE FROM tab1 WHERE at1=at2;

DELETE FROM tab1 WHERE at1=at2

*

ERROR at line 1:

ORA-04091: table U1.TAB1 is mutating, trigger/function may not see it

ORA-06512: at "U1.TR1", line 2

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

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

Пусть таблица tab1 имеет один столбец at1 и пять строк:

CREATE TABLE tab1 (at1 INTEGER)

SQL> SELECT * FROM tab1;

AT1

–

1

2

3 – смотрим срабатывание триггера на этой строке

0

4

Выполняем предложение

UPDATE tab2 SET at1=at1+1;

Пусть на каждой обрабатываемой строке срабатывает AFTER-триггер уровня строки, в коде которого выполняется запрос

SELECT COUNT(*) FROM tab2 WHERE at1<3

Смотрим результаты этого SQL-запроса для строки с тройкой при двух разных вариантах порядка обработки строк (o,old – старое значение, n,new – новое значение):

Первый вариант порядка обработки строк

Второй вариант порядка обработки строк

|

o1-> n2

o3-> n4 COUNT:return 3:row(n2,o2,o0)

o2-> o2 (пока не менялось)

o0-> o0 (пока не менялось)

o4-> o4 (пока не менялось)

|

o1-> n2

o4-> n5

o0-> n1

o2-> n3

o3-> n4 COUNT:return 2:row(n2,n1)

Для первого варианта порядка обработки строк (1,3,2,0,4) SQL-запрос в теле триггера возвращает число 3 для COUNT(*), для второго варианта (1,4,0,2,3) – число 2 (3<>2). То есть один и тот же запрос при одинаковом исходном содержимом таблицы в ходе срабатывания триггера на одной и той же строке может вернуть различные результаты.

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

Запрет доступа к мутирующим таблицам относится только к триггерам уровня строки. Триггеры уровня предложения SQL могут и считывать, и записывать данные мутирующей таблицы. Это понятно – перед триггером уровня предложения «лежит» множество всех строк, обрабатываемых предложением SQL. Для AFTER-триггера они все уже обработаны, для BEFORE-триггера они все еще не обработаны. В таких условиях действия с данными в мутирующей таблице в триггере при любом исходном порядке строк в таблице будут завершаться с одинаковыми результатами.