Триггеры PL/SQL уровня команд DML на примерах

Триггеры PL/SQL уровня команд DML (или просто триггеры DML) активизируются после вставки, обновления или удаления строк конкретной таблицы (рис. 1). Это самый распространенный тип триггеров, особенно часто применяемый разработчиками. Остальные триггеры используются преимущественно администраторами базы данных Oracle. В Oracle появилась возможность объединения нескольких триггеров DML в один составной триггер.

Прежде чем браться за написание триггера, необходимо ответить на следующие вопросы:

•  Как триггер PL/SQL будет запускаться — по одному разу для каждой команды SQL или для каждой модифицируемой ею строки?
•  Когда именно должен вызываться создаваемый триггер — до или после выполнения операции над строками?
•  Для каких операций должен срабатывать триггер — вставки, обновления, удаления или их определенной комбинации?

Рис. 1. Схема срабатывания триггеров DML

Основные концепции триггеров

Прежде чем переходить к синтаксису и примерам использования триггеров DML, сле­дует познакомиться с их концепциями и терминологией.

•  Триггер BEFORE. Вызывается до внесения каких-либо изменений (например, BEFORE INSERT).
•  Триггер AFTER. Выполняется для отдельной команды SQL, которая может обраба­тывать одну или более записей базы данных (например, AFTER UPDATE).
•  Триггер уровня команды. Выполняется для команды SQL в целом (которая может обрабатывать одну или несколько строк базы данных).
•  Триггер уровня записи. Выполняется для отдельной записи, обрабатываемой ко­мандой SQL. Если, предположим, таблица books содержит 1000 строк, то следующая команда UPDATE модифицирует все эти строки:

UPDATE books SET title = UPPER (title);

И если для таблицы определен триггер уровня записи, он будет выполнен 1000 раз.

•  Псевдозапись NEW. Структура данных с именем NEW  так же выглядит и обладает (почти) такими же свойствами, как запись PL/SQL. Эта псевдозапись доступна только внутри триггеров обновления и вставки; она содержит значения модифици­рованной записи после внесения изменений.
•  Псевдозапись OLD. Структура данных с именем OLD так же выглядит и обладает (почти) такими же свойствами, как запись PL/SQL. Эта псевдозапись доступна только внутри триггеров обновления и вставки; она содержит значения модифици­рованной записи до внесения изменений.
•  Секция WHEN. Часть триггера DML, определяющая условия выполнения кода триггера (и позволяющая избежать лишних операций).

Примеры сценариев с использованием триггеров DML

На сайте github размещены примеры сценариев, демонстрирующих работу описанных в предыдущем разделе типов триггеров.

Триггеры в транзакциях

По умолчанию триггеры DML участвуют в транзакциях, из которых они запущены. Это означает, что:

•  если триггер инициирует исключение, будет выполнен откат соответствующей части транзакции;
•  если триггер сам выполнит команду DML (например, вставит запись в таблицу- журнал), она станет частью главной транзакции;
•  в триггере DML нельзя выполнять команды COMMIT и ROLLBACK.

Если триггер DML определен как автономная транзакция PL/SQL, то все команды DML, выполняемые внутри триггера, будут сохраняться или отменяться (командой COMMIT или ROLLBACK) независимо от основной транзакции.

В следующем разделе описан синтаксис объявления триггера DML и приведен пример, в котором использованы многие компоненты и параметры триггеров этого типа.

Создание триггера DML

Команда создания (или замены) триггера DML имеет следующий синтаксис:

1	CREATE [OR REPLACE] TRIGGER имя_триггера
2	{BEFORE | AFTER}
3	{INSERT | DELETE | UPDATE | UPDATE OF список_столбцов } ON имя_таблицы
4	[FOR EACH ROW]
5	[WHEN (...)]
6	[DECLARE ... ]
7	BEGIN
8	...исполняемые команды...
9	[EXCEPTION ... ]
10	END [имя_триггера];

Описание всех перечисленных элементов приведено в таблице.

Рассмотрим пару примеров триггеров DML.

•  Первый триггер выполняет несколько проверок при добавлении или изменении строки в таблице сотрудников. В нем содержимое полей псевдозаписи NEW передается отдельным программам проверки:

TRIGGER validate_employee_changes 
   AFTER INSERT OR UPDATE 
   ON employees 
   FOR EACH ROW 
BEGIN
   check_date (:NEW.hire_date); 
   check_email (:NEW.email);
END;

•  Следующий триггер, запускаемый перед вставкой данных, проверяет изменения, производимые в таблице ceo_compensation. Для сохранения новой строки таблицы аудита вне главной транзакции в нем используется технология автономных тран­закций:

TRIGGER bef_ins_ceo_comp 
   BEFORE INSERT 
   ON ceo_compensation 
   FOR EACH ROW 
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO ceo_comp_history
      VALUES (:NEW.name,
         :OLD.compensation, :NEW.compensation,
         'AFTER INSERT', SYSDATE);
   COMMIT;
END;

Предложение WHEN

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

TRIGGER check_raise
   AFTER UPDATE OF salary 
   ON employees 
   FOR EACH ROW
WHEN ((OLD.salary != NEW.salary) OR
   (OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR 
   (OLD.salary IS NOT NULL AND NEW.salary IS NULL))
BEGIN

Иными словами, если при обновлении записи пользователь по какой-то причине оставит salary текущее значение, триггер активизируется, но его основной код выполняться не будет. Проверяя это условие в предложении WHEN, можно избежать затрат, связанных с запуском соответствующего кода PL/SQL.

В файле genwhen.sp на сайте github представлена процедура для генерирования секции WHEN, которая проверяет, что новое значение действительно отличается от старого.

В большинстве случаев секция WHEN содержит ссылки на поля псевдозаписей NEW и OLD. В эту секцию разрешается также помещать вызовы встроенных функций, что и делается в следующем примере, где с помощью функции SYSDATE ограничивается время вставки новых записей:

TRIGGER valid_when_clause 
BEFORE INSERT ON frame 
FOR EACH ROW
WHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 )

При использовании WHEN следует соблюдать ряд правил:

•  Все логические выражения всегда должны заключаться в круглые скобки. Эти скоб­ки не обязательны в команде IF, но необходимы в секции WHEN триггера.
•  Перед идентификаторами OLD и NEW не должно стоять двоеточие (:). В секции WHEN следует использовать только встроенные функции.
•  Пользовательские функции и функции, определенные во встроенных пакетах (таких, как DBMS_UTILITY), в нем вызывать нельзя. Чтобы вызвать такую функцию, переме­стите соответствующую логику в начало исполняемого раздела триггера.

Предложение WHEN может использоваться только в триггерах уровня записи. Поместив его в триггер уровня команды, вы получите сообщение об ошибке ком­пиляции (ORA-04077).

Работа с псевдозаписями NEW и OLD

При запуске триггера уровня записи ядро PL/SQL создает и заполняет две структуры данных, имеющие много общего с записями. Речь идет о псевдозаписях NEW и OLD (пре­фикс «псевдо» указывает на то, что они не обладают всеми свойствами записей PL/SQL). В псевдозаписи OLD хранятся исходные значения обрабатываемой триггером записи, а в псевдозаписи NEW — новые. Их структура идентична структуре записи, объявленной с атрибутом %ROWTYPE и создаваемой на основе таблицы, с которой связан триггер. Несколько правил, которые следует принимать во внимание при работе с псевдозапи­сями NEW и OLD:

•  Для триггеров, связанных с командой INSERT, структура OLD не содержит данных, поскольку старого набора значений у операции вставки нет.
•  Для триггеров, связанных с командой UPDATE, заполняются обе структуры, OLD и NEW. Структура OLD содержит исходные значения записи до обновления, а NEW — значения, которые будут содержаться в строке после обновления.
•  Для триггеров, связанных с командой DELETE, заполняется только структура OLD, а структура NEW остается пустой, поскольку запись удаляется.
•  Псевдозаписи NEW и OLD также содержат столбец ROWID, который в обеих псевдозапи­сях всегда заполняется одинаковыми значениями.
•  Значения полей записи OLD изменять нельзя; попытка приведет к ошибке ORA- 04085. Значения полей структуры NEW модифицировать можно.
•  Структуры NEW и OLD нельзя передавать в качестве параметров процедурам или функ­циям, вызываемым из триггера. Разрешается передавать лишь их отдельные поля. В сценарии gentrigrec.sp содержится программа, которая генерирует код копирования данных NEW и OLD в записи, передаваемые в параметрах.
•  В ссылках на структуры NEW и OLD в анонимном блоке триггера перед соответствую­щими ключевыми словами необходимо ставить двоеточие:

IF :NEW.salary > 10000 THEN...

•  Над структурами NEW и OLD нельзя выполнять операции уровня записи. Например, следующая команда вызовет ошибку компиляции триггера:

BEGIN :new := NULL; END;

С помощью секции REFERENCING в триггере можно менять имена псевдозаписей данных; это помогает писать самодокументированный код, ориентированный на конкретное приложение. Пример:

/* Файл в Сети: full_old_and_new.sql */
TRIGGER audit_update 
   AFTER UPDATE 
   ON frame
   REFERENCING OLD AS prior_to_cheat NEW AS after_cheat 
   FOR EACH ROW 
BEGIN
   INSERT INTO frame_audit 
               (bowler_id, 
               game_id, 
               old_score, 
               new_score, 
               change_date, 
               operation)
        VALUES(:after_cheat.bowler_id,
               :after_cheat.game_id,
               :prior_to_cheat.score,
               :after_cheat.score,
               SYSDATE,
               'UPDATE');
END;

Запустите файл сценария full_old_and_new.sql и проанализируйте поведение псевдозапи­сей NEW и OLD.

Идентификация команды DML в триггере

Oracle предоставляет набор функций (также называемых операционными директивами) для идентификации команды DML, вызвавшей запуск триггера:

•  INSERTING — возвращает TRUE, если триггер запущен в ответ на вставку записи в та­блицу, с которой он связан, и FALSE в противном случае.
•  UPDATING — возвращает TRUE, если триггер запущен в ответ на обновление записи в таблице, с которой он связан, и FALSE в противном случае.
•  DELETING — возвращает TRUE, если триггер запущен в ответ на удаление записи из таблицы, с которой он связан, и FALSE в противном случае.

Пользуясь этими директивами, можно создать один триггер, который объединяет дей­ствия для нескольких операций. Пример:

/* Файл в Сети: one_trigger_does_it_all.sql */
TRIGGER three_for_the_price_of_one
BEFORE DELETE OR INSERT OR UPDATE ON account_transaction 
FOR EACH ROW
BEGIN
   -- Сохранение информации о пользователе, вставившем новую строку
   IF INSERTING
   THEN
      :NEW.created_by := USER;
      :NEW.created_date := SYSDATE;
   -- Сохранение информации об удалении с помощью специальной программы
   ELSIF DELETING
   THEN
      audit_deletion(USER,SYSDATE);
   -- Сохранение информации о пользователе, который последним обновлял строку
   ELSIF UPDATING
   THEN
      :NEW.UPDATED_BY := USER;
      :NEW.UPDATED_DATE := SYSDATE;
   END IF;
END;

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

/* Файл в Сети: overloaded_update.sql */
TRIGGER validate_update
BEFORE UPDATE ON account_transaction
FOR EACH ROW
BEGIN
   IF UPDATING ('ACCOUNT_NO')
   THEN
      errpkg.raise('Account number cannot be updated');
   END IF;
END;

В спецификации имени столбца игнорируется регистр символов. Имя столбца до за­пуска триггера не анализируется, и если в таблице, связанной с триггером, заданного столбца не оказывается, функция просто возвращает FALSE.

Операционные директивы можно вызывать из любого кода PL/SQL, а не только из триггеров. Однако значение TRUE они возвращают лишь при использовании в триггерах DML или вызываемых из них программах.

Пример триггера DML

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

В приложении Памелы центральное место занимает таблица frame, в которой записы­вается результат конкретного фрейма конкретной партии конкретного игрока:

/* Файл в Сети: bowlerama_tables.sql */
TABLE frame 
(bowler_id      NUMBER,
game_id	        NUMBER,
frame_number	NUMBER,
strike	        VARCHAR2(1)	DEFAULT	'N',
spare	        VARCHAR2(1)	DEFAULT	'N',
score	        NUMBER,
CONSTRAINT frame_pk
PRIMARY KEY (bowler_id, game_id, frame_number))

Памела дополняет таблицу frame версией, в которой сохраняются все значения «до» и «после», чтобы она могла сравнить их и выявить несоответствия:

TABLE frame_audit
(bowler_id    NUMBER,
game_id       NUMBER,
frame_number  NUMBER,
old_strike    VARCHAR2(1),
new_strike    VARCHAR2(1),
old_spare     VARCHAR2(1),
new_spare     VARCHAR2(1),
old_score     NUMBER,
new_score     NUMBER,
change_date   DATE,
operation     VARCHAR2(6))

Для каждого изменения в таблице frame Памела хочет отслеживать состояние строки до и после изменения. Она создает простой триггер:

/* Файл в Сети: bowlerama_full_audit.sql */
1    TRIGGER audit_frames
2    AFTER INSERT OR UPDATE OR DELETE ON frame
3    FOR EACH ROW
4    BEGIN
5       IF INSERTING THEN
6        INSERT INTO frame_audit(bowler_id,game_id,frame_number,
7                                  new_strike,new_spare,new_score,
8                                  change_date,operation)
9       VALUES(:NEW.bowler_id,:NEW.game_id,:NEW.frame_number,
10             :NEW.strike,:NEW.spare,:NEW.score,
11             SYSDATE,'INSERT');
12
13       ELSIF UPDATING THEN
14          INSERT INTO frame_audit(bowler_id,game_id,frame_number,
15                                  old_strike,new_strike,
16                                  old_spare,new_spare,
17                                  old_score,new_score,
18                                  change_date,operation)
19       VALUES(:NEW.bowler_id,:NEW.game_id,:NEW.frame_number,
20                :OLD.strike,:NEW.strike,
21                :OLD.spare,:NEW.spare,
22                :OLD.score,:NEW.score,
23                SYSDATE,'UPDATE');
24
25       ELSIF DELETING THEN
26          INSERT INTO frame_audit(bowler_id,game_id,frame_number,
27                                  old_strike,old_spare,old_score,
28                                  change_date,operation)
29          VALUES(:OLD.bowler_id,:OLD.game_id,:OLD.frame_number,
30                 :OLD.strike,:OLD.spare,:OLD.score,
31                 SYSDATE,'DELETE');
32       END IF;
33    END audit_frames;

В секции INSERTING (строки 6-11) для заполнения строки аудита используется псев­дозапись NEW. Для UPDATING (строки 14-23) используется сочетание информации NEW и OLD. Для DELETING (строки 26-31) доступна только информация OLD. Памела создает триггер и ждет результатов.

Конечно, она не распространяется о своей новой системе. Салли — амбициозный, но не очень искусный игрок — понятия не имеет, что ее действия могут отслеживаться. Салли решает, что в этом году она должна стать чемпионом, и она не остановится ни перед чем. У нее есть доступ к SQI*Plus, и она знает, что ее идентификатор игрока равен 1. Салли располагает достаточной информацией, чтобы полностью обойти графический интерфейс, подключиться к SQL*Plus и пустить в ход свое беспринципное «волшебство». Салли сходу выписывает себе страйк в первом фрейме:

SQL> INSERT INTO frame
   2 (BOWLER_ID,GAME_ID,FRAME_NUMBER,STRIKE)
   3 VALUES(1,1,1,'Y');
1 row created.

Но затем она решает умерить аппетит и понижает результат первого фрейма, чтобы вызвать меньше подозрений:

SQL> UPDATE frame
   2 SET strike = 'N',
   3 spare = 'Y'
   4 WHERE bowler_id = 1
   5 AND game_id = 1
   6 AND frame_number = 1;
1 row updated.

Но что это? Салли слышит шум в коридоре. Она теряет самообладание и пытается замести следы:

SQL> DELETE frame
   2 WHERE bowler_id = 1
   3 AND game_id = 1
   4 AND frame_number = 1;
1 row deleted.

SQL> COMMIT;
Commit complete.

Она даже убеждается в том, что ее исправления были удалены:

SQL> SELECT * FROM frame;
no rows selected

Вытирая пот со лба, Салли завершает сеанс, но рассчитывает вернуться и реализовать свои планы.

Бдительная Памела подключается к базе данных и моментально обнаруживает, что пыталась сделать Салли. Для этого она выдает запрос к таблице аудита (кстати, Памела также может настроить ежечасный запуск задания через DBMS_JOB для автоматизации этой части аудита):

SELECT bowler_id, game_id, frame_number 
      , old_strike, new_strike 
      , old_spare, new_spare 
      , change_date, operation 
   FROM frame_audit

Результат:

Салли поймана с поличным! Из записей аудита прекрасно видно, что она пыталась сделать, хотя в таблице frame никаких следов не осталось. Все три команды — исходная вставка записи, понижение результата и последующее удаление записи — были пере­хвачены триггером DMI.

Применение секции WHEN

После того как система аудита успешно проработала несколько месяцев, Памела при­нимает меры для дальнейшего устранения потенциальных проблем. Она просматривает интерфейсную часть своего приложения и обнаруживает, что изменяться могут только поля strike, spare и score. Следовательно, триггер может быть и более конкретным:

TRIGGER audit_update
   AFTER UPDATE OF strike, spare, score 
   ON frame
   REFERENCING OLD AS prior_to_cheat NEW AS after_cheat 
   FOR EACH ROW 
BEGIN
   INSERT INTO frame_audit (...)
      VALUES (...);
END;

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

SQL> UPDATE FRAME
   2	SET strike = strike;
      1	row updated.
SQL> SELECT old_strike,
2	new_strike,
3	old_spare,
4	new_spare,
5	old_score,
6	new_score
7   FROM frame_audit;
O N O N OLD SCORE    NEW SCORE
- - - - ---------    ---------
Y Y N N

Триггер нужно дополнительно уточнить, чтобы он срабатывал только при фактическом изменении значений. Для этого используется секция WHEN:

/* Файл в Сети: final_audit.sql */
TRIGGER audit_update
AFTER UPDATE OF STRIKE, SPARE, SCORE ON FRAME 
REFERENCING OLD AS prior_to_cheat NEW AS after_cheat 
FOR EACH ROW
WHEN ( prior_to_cheat.strike != after_cheat.strike OR 
       prior_to_cheat.spare != after_cheat.spare OR 
       prior_to_cheat.score != after_cheat.score )
BEGIN
   INSERT INTO FRAME_AUDIT ( ... )
      VALUES ( ... );
END;

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

SQL> UPDATE frame
   2 SET strike = strike;
1 row updated.

SQL> SELECT old_strike, 
   2	   new_strike,
   3	   old_spare,
   4       new_spare,
   5	   old_score,
   6 	   new_score
   7   FROM frame_audit; 
no rows selected

Использование псевдозаписей для уточнения триггеров

Памела реализовала в системе приемлемый уровень аудита; теперь ей хотелось бы сделать систему более удобной для пользователя. Самая очевидная идея — сделать так, чтобы система сама увеличивала счет во фреймах, заканчивающихся страйком или спэром, на 10. Это позволяет счетчику отслеживать счет только за последующие броски, а счет за страйк будет начисляться автоматически:

/* Файл в Сети: set_score.sql */
TRIGGER set_score 
BEFORE INSERT ON frame 
FOR EACH ROW
WHEN ( NEW.score IS NOT NULL )
BEGIN
   IF :NEW.strike = 'Y' OR :NEW.spare = 'Y'
   THEN
      :NEW.score := :NEW.score + 10;
   END IF;
END;

Помните, что значения полей в записях NEW могут изменяться только в BEFORE- триггерах строк.

Будучи человеком пунктуальным, Памела решает добавить проверку счета в ее набор триггеров:

/* File on Сети: validate_score.sql */
TRIGGER validate_score 
   AFTER INSERT OR UPDATE 
   ON frame 
   FOR EACH ROW 
BEGIN
   IF :NEW.strike = 'Y' AND :NEW.score < 10 
   THEN
      RAISE_APPLICATION_ERROR (
         -20001,
         'ERROR: Score For Strike Must Be >= 10'
      );
   ELSIF :NEW.spare = 'Y' AND :NEW.score < 10 
   THEN
      RAISE_APPLICATION_ERROR (
         -20001,
         'ERROR: Score For Spare Must Be >= 10'
      );
   ELSIF :NEW.strike = 'Y' AND :NEW.spare = 'Y'
   THEN
      RAISE_APPLICATION_ERROR (
         -20001,
         'ERROR: Cannot Enter Spare And Strike'
      );
   END IF;
END;

Теперь любая попытка ввести строку, нарушающую это условие, будет отклонена:

SQL> INSERT INTO frame VALUES(1,1J1J'Y'JNULLJ5);
   2 INSERT INTO frame *

ERROR at line 1:
ORA-20001: ERROR: Score For Strike Must >= 10

Однотипные триггеры

Oracle позволяет связать с таблицей базы данных несколько триггеров одного типа. Рассмотрим такую возможность на примере, связанном с игрой в гольф. Следующий триггер уровня строки вызывается при вставке в таблицу новой записи и добавляет в нее комментарий, текст которого определяется соотношением текущего счета с но­минальным значением 72:

/* Файл в Сети: golf_commentary.sql */
TRIGGER golf_commentary 
   BEFORE INSERT 
   ON golf_scores 
   FOR EACH ROW 
DECLARE
   c_par_score CONSTANT PLS_INTEGER := 72;
BEGIN
   :new.commentary :=
         CASE
            WHEN :new.score < c_par_score THEN 'Under'
            WHEN :new.score = c_par_score THEN NULL 
            ELSE 'Over' || ' Par'
      END;
END;

Эти же действия можно выполнить и с помощью трех отдельных триггеров уровня строки типа BEFORE INSERT с взаимоисключающими условиями, задаваемыми в секциях WHEN:

TRIGGER golf_commentary_under_par 
BEFORE INSERT ON golf_scores 
FOR EACH ROW 
WHEN (NEW.score < 72)
BEGIN
   :NEW.commentary := 'Under Par';
END;
TRIGGER golf_commentary_par 
BEFORE INSERT ON golf_scores 
FOR EACH ROW 
WHEN (NEW.score = 72)
BEGIN
   :NEW.commentary := 'Par';
END;
TRIGGER golf_commentary_over_par 
BEFORE INSERT ON golf_scores 
FOR EACH ROW 
WHEN (NEW.score > 72)
BEGIN
   :NEW.commentary := 'Over Par';
END;

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

Очередность вызова триггеров

До выхода Oraclellg порядок срабатывания нескольких триггеров DML был непред­сказуемым. В рассмотренном примере он несущественен, но как показывает следующий пример, в других ситуациях могут возникнуть проблемы. Какой результат будет получен для последнего запроса?

/* Файл в Сети: multiple_trigger_seq.sql */
TABLE incremented_values 
(value_inserted NUMBER, 
value_incremented NUMBER);

TRIGGER increment_by_one
BEFORE INSERT ON incremented_values
FOR EACH ROW
BEGIN
   :NEW.value_incremented := :NEW.value_incremented + 1;
END;

TRIGGER increment_by_two
BEFORE INSERT ON incremented_values
FOR EACH ROW
BEGIN
   IF :NEW.value_incremented > 1 THEN
      :NEW.value_incremented := :NEW.value_incremented + 2;
   END IF;
END;

INSERT INTO incremented_values 
   VALUES(1,1);
SELECT *
      FROM incremented_values;
SELECT *
      FROM incremented_values;

Есть какие-нибудь предположения? Для моей базы данных результаты получились такими:

SQL> SELECT *
   2	FROM incremented_values;

VALUE INSERTED VALUE INCREMENTED
-------------- -----------------
             1	               2

Это означает, что первым сработал триггер increment_by_two, который не выполнил ни­каких действий, потому что значение столбца value_incremented не превышало 1; затем сработал триггер increment_by_one, увеличивший значение столбца value_incremented на 1. А вы тоже получите такой результат? Вовсе не обязательно. Будет ли этот результат всегда одним и тем же? Опять-таки, ничего нельзя гарантировать. До выхода Oracle11g в документации Oracle было явно указано, что порядок запуска однотипных триггеров, связанных с одной таблицей, не определен и произволен, поэтому задать его явно невоз­можно. На этот счет существуют разные теории, среди которых наиболее популярны две: триггеры запускаются в порядке, обратном порядку их создания или же в соответствии с идентификаторами их объектов, но полагаться на такие предположения не стоит. Начиная с Oracle11g гарантированный порядок срабатывания триггеров может опре­деляться при помощи условия FOLLOWS, как показано в следующем примере:

TRIGGER increment_by_two
BEFORE INSERT ON incremented_values
FOR EACH ROW
FOLLOWS increment_by_one 
BEGIN
   IF :new.value_incremented > 1 THEN
      :new.value_incremented := :new.value_incremented + 2;
   END IF;
END;

Теперь этот триггер заведомо будет активизирован раньше триггера increment_by_one. Тем самым гарантируется и результат вставки:

SQL> INSERT INTO incremented_values 
   2 VALUES(1,1);
1 row created.
SQL> SELECT *
   2	FROM incremented_values;
VALUE INSERTED VALUE INCREMENTED
-------------- -----------------
             1	               4

Триггер increment_by_one увеличил вставленное значение до 2, а триггер increment_by_two увеличил его до 4. Такое поведение гарантировано, потому что оно определяется на уровне самого триггера — нет необходимости полагаться на догадки и предположения. Связи последовательности триггеров можно просмотреть в представлении зависимостей словаря данных Oracle:

SQL> SELECT     referenced_name,
   2            referenced_type,
   3            dependency_type
   4      FROM user_dependencies
   5   WHERE name = 'INCREMENT_BY_TWO'
   6      AND referenced_type = 'TRIGGER';
REFERENCED  NAME   REFERENCED TYPE   DEPE
----------------   ---------------   ----
INCREMENT_BY_ONE   TRIGGER	     REF

Несмотря на поведение, описанное выше для Oracle Database 11g, при попытке отком­пилировать триггер, следующий за неопределенным триггером, выводится сообщение об ошибке:

Trigger "SCOTT"."BLIND_FOLLOWER" referenced in FOLLOWS or PRECEDES clause may not 
exist

Ошибки при изменении таблицы

Изменяющиеся объекты трудно анализировать и оценивать. Поэтому когда триггер уровня строки пытается прочитать или изменить данные в таблице, находящейся в со­стоянии изменения (с помощью команды INSERT, UPDATE или DELETE), происходит ошибка с кодом ORA-4091.

В частности, эта ошибка встречается тогда, когда триггер уровня строк пытается вы­полнить чтение или запись в таблицу, для которой сработал триггер. Предположим, для таблицы сотрудников требуется задать ограничение на значения в столбцах, которое заключается в том, что при повышении оклада сотрудника его новое значение не должно превышать следующее значение по отделу более чем на 20%.

Казалось бы, для проверки этого условия можно использовать следующий триггер:

TRIGGER brake_on_raises
   BEFORE UPDATE OF salary ON employee 
   FOR EACH ROW 
DECLARE
   l_curr_max NUMBER;
BEGIN
   SELECT MAX (salary) INTO l_curr_max 
      FROM employee;
   IF l_curr_max * 1.20 < :NEW.salary 
   THEN
      errpkg.RAISE (
         employee_rules.en_salary_increase_too_large,
         :NEW.employee_id,
         :NEW.salary
      );
   END IF;
END;

Однако при попытке удвоить, скажем, оклад программиста PL/SQL, Oracle выдаст сообщение об ошибке:

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

Тем не менее некоторые приемы помогут предотвратить выдачу этого сообщения об ошибке:

•  В общем случае триггер уровня строки не может считывать или записывать данные таблицы, с которой он связан. Но подобное ограничение относится только к тригге­рам уровня строки. Триггеры уровня команд могут и считывать, и записывать данные своей таблицы, что дает возможность произвести необходимые действия.
•  Если триггер выполняется как автономная транзакция (директива PRAGMA AUTONOMOUS TRANSACTION и выполнение COMMIT в теле триггера), тогда в нем можно запрашивать содержимое таблицы. Однако модификация такой таблицы все равно будет запре­щена.

С каждым выпуском Oracle проблема ошибок изменения таблицы становится все менее актуальной, поэтому мы не станем приводить полное описание. На сайте github размещен демонстрационный сценарий mutation_zone.sql. Кроме того, в файле mutating_template.sql представлен пакет, который может послужить шаблоном для создания вашей собствен­ной реализации перевода логики уровня записей на уровень команд.

Составные триггеры

По мере создания триггеров, содержащих все больший объем бизнес-логики, становится трудно следить за тем, какие триггеры связаны с теми или иными правилами и как триг­геры взаимодействуют друг с другом. В предыдущем разделе было показано, как три типа команд DML (вставка, обновление, удаление) объединяются в одном триггере, но разве не удобно было бы разместить триггеры строк и команд вместе в одном объекте кода? В Oracle Database 11g появилась возможность использования составных триггеров для решения этой задачи. Следующий простой пример демонстрирует этот синтаксис:

   /* Файл в Сети: compound_trigger.sql */
1   TRIGGER compounder
2   FOR UPDATE OR INSERT OR DELETE ON incremented_values
3   COMPOUND TRIGGER
4
5      v_global_var NUMBER := 1;
6
7      BEFORE STATEMENT IS
8      BEGIN
9         DBMS_OUTPUT.PUT_LINE('Compound:BEFORE S:' || v_global_var);
10         v_global_var := v_global_var + 1;
11      END BEFORE STATEMENT;
12
13      BEFORE EACH ROW IS
14	BEGIN
15         DBMS_OUTPUT.PUT_LINE('Compound:BEFORE R:' || v_global_var);
16         v_global_var := v_global_var + 1;
17      END BEFORE EACH ROW;
18
19      AFTER EACH ROW IS
20      BEGIN
21         DBMS_OUTPUT.PUT_LINE('Compound:AFTER	R:'	||	v_global_var);
22	   v_global_var := v_global_var + 1;
23      END AFTER EACH ROW;
24
25      AFTER STATEMENT IS
26	BEGIN
27         DBMS_OUTPUT.PUT_LINE('Compound:AFTER	S:'	||	v_global_var);
28         v_global_var := v_global_var + 1;
29	END AFTER STATEMENT;
30
31   END;

Сходство с пакетами

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

Самое очевидное изменение — конструкция COMPOUND TRIGGER, сообщающая Oracle, что триггер содержит несколько триггеров, которые должны срабатывать вместе. Следующее (и пожалуй, самое долгожданное) изменение встречается в строке 5: гло­бальная переменная! Наконец-то глобальные переменные могут определяться вместе с кодом, который с ними работает, — специальные пакеты для них больше не нужны:

PACKAGE BODY yet_another_global_package AS 
   v_global_var NUMBER := 1;
   PROCEDURE reset_global_var IS
   ...
END;

В остальном синтаксис составных триггеров очень похож на синтаксис автономных триггеров, но не так гибок:

•  BEFORE STATEMENT — код этого раздела выполняется до команды DML, как и в случае с автономным триггером BEFORE.
•  BEFORE EACH ROW — код этого раздела выполняется перед обработкой каждой строки командой DML.
•  AFTER EACH ROW — код этого раздела выполняется после обработки каждой строки командой DML.
•  AFTER STATEMENT — код этого раздела выполняется после команды DML, как и в слу­чае с автономным триггером AFTER.

Правила автономных триггеров также применимы и к составным триггерам — например, значения записей (OLD и NEW) не могут изменяться в триггерах уровня команд.

Различия с пакетами

Итак, составные триггеры похожи на пакеты PL/SQL, но означает ли это, что они так же рабо­тают? Нет — они работают лучше! Рассмотрим следующий пример:

SQL> BEGIN
   2      insert into incremented_values values(1,1);
   3      insert into incremented_values values(2,2);
   4   END;
   5   / 
Compound:BEFORE   S:1 
Compound:BEFORE   R:2 
Compound:AFTER    R:3 
Compound:AFTER    S:4 
Compound:BEFORE   S:1 
Compound:BEFORE   R:2 
Compound:AFTER    R:3 
Compound:AFTER    S:4

PL/SQL procedure successfully completed.

Обратите внимание: при выполнении второй команды для глобальной переменной снова выводится 1. Это связано с тем, что область действия составного триггера огра­ничивается командой DML, которая его инициирует. После завершения этой команды составной триггер и его значения, хранящиеся в памяти, перестают существовать. Это обстоятельство упрощает логику.

Дополнительным преимуществом ограниченной области действия является упрощен­ная обработка ошибок. Чтобы продемонстрировать это обстоятельство, я определяю в таблице первичный ключ для последующего нарушения:

SQL> ALTER TABLE incremented_values
   2   add constraint a_pk
   3   primary key ( value_inserted );

Теперь вставим одну запись:

SQL> INSERT INTO incremented_values values(1,1);
Compound:BEFORE S:1 
Compound:BEFORE R:2 
Compound:AFTER R:3 
Compound:AFTER S:4 
1 row created.

Пока без сюрпризов. Но следующая команда INSERT выдает ошибку из-за нарушения нового первичного ключа:

SQL> INSERT INTO incremented_values values(1,1);
Compound:BEFORE S:1 
Compound:BEFORE R:2
insert into incremented_values values(1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.A_PK) violated

Следующая команда INSERT также снова выдает ошибку первичного ключа. Но в этом как раз ничего примечательного нет — примечательно то, что глобальная переменная была снова инициализирована значением 1 без написания дополнительного кода. Команда DML завершилась, составной триггер вышел из области действия, и со следующей командой все начинается заново:

SQL> INSERT INTO incremented_values values(1,1);
Compound:BEFORE S:1 
Compound:BEFORE R:2
insert into incremented_values values(1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (DRH.A_PK) violated

 Теперь мне не нужно включать дополнительную обработку ошибок или пакеты только для сброса значений при возникновении исключения.

FOLLOWS с составными триггерами

Составные триггеры также могут использоваться с синтаксисом FOLLOWS:

TRIGGER follows_compounder
BEFORE INSERT ON incremented_values
FOR EACH ROW
FOLLOWS compounder
BEGIN
   DBMS_OUTPUT.PUT_LINE('Following Trigger');
END;

Результат:

SQL> INSERT INTO incremented_values 
   2 values(8,8);
Compound:BEFORE S:1 
Compound:BEFORE R:2 
Following Trigger 
Compound:AFTER R:3 
Compound:AFTER S:4 
1 row created.

Конкретные триггеры, находящиеся внутри составного триггера, не могут определяться как срабатывающие после каких-либо автономных или составных триггеров.

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

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL... 4651 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48

Встроенные методы коллекций PL... 14849 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Тип данных RAW в PL/SQL 12353 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Символьные функции и аргументы... 18590 просмотров Анатолий Wed, 23 May 2018, 18:54:01