Язык PL/SQL

выполнение DML-предложений SQL добавления, изменения и удаления данных таблиц в таблицах – INSERT, UPDATE, DELETE;

выполнение DDL-команд (команд создания, изменения и удаления объектов базы данных – CREATE, ALTER, DROP и некоторых других);

события уровня базы данных (запуск и остановка базы данных, возникновение системных ошибок и т. п.).

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

Триггеры на выполнение 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 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 транзакции.

При наличии 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%.

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

Мутирующая таблица (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)