Триггеры 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 [имя_триггера];
Описание всех перечисленных элементов приведено в таблице.
Строки | Описание |
1 | Создание триггера с заданным именем. Секция OR REPLACE не обязательна. Если триггерсуществует, а секция REPLACE отсутствует, попытка создания триггера приведет к ошибке ORA-4081 . Вообще говоря, триггер и таблица могут иметь одинаковые имена (а также триггер и процедура), но мы рекомендуем использовать схемы выбора имен, предотвращающиеподобные совпадения с неизбежной путаницей |
2 | Задание условий запуска триггера: до (BEFORE ) или после (AFTER ) выполнения командылибо обработки строки |
3 | Определение команды DML, с которой связывается триггер: INSERT, UPDATE или DELETE .Обратите внимание: триггер, связанный с командой UPDATE, может быть задан для всей строки или только для списка столбцов, разделенных запятыми. Столбцы можно объединять оператором OR и задавать в любом порядке. Кроме того, в строке 3 определяется таблица, с которой связывается данный триггер. Помните, что каждый триггер DML должен быть связан с одной таблицей |
4 | Если задана секция FOR EACH ROW , триггер будет запускаться для каждой обрабатываемойкомандой строки. Но если эта секция отсутствует, по умолчанию триггер будет запускаться только по одному разу для каждой команды (то есть будет создан триггер уровня команды) |
5 | Необязательная секция WHEN , позволяющая задать логику для предотвращения в лишнихвыполнений триггера |
6 | Необязательный раздел объявлений для анонимного блока, составляющего код триггера. Если объявлять локальные переменные не требуется, это ключевое слово может отсутствовать. Никогда не объявляйте псевдозаписи NEW и OLD — они создаются автоматически |
7,8 | Исполняемый раздел триггера. Он является обязательным и должен содержать как минимум одну команду |
9 | Необязательный раздел исключений. В нем перехватываются и обрабатываются исключения, инициируемые только в исполняемом разделе |
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
Результат:
BOWLER_ID | GAME_ID | FRAME_NUMBER | O | N | O | N | CHANGE_DA | OPERAT |
1 | 1 | 1 | Y | N | 12-SEP-00 | INSERT | ||
1 | 1 | 1 | Y | N | N | Y | 12-SEP-00 | UPDATE |
1 | 1 | 1 | N | N | 12-SEP-00 | DELETE |
Салли поймана с поличным! Из записей аудита прекрасно видно, что она пыталась сделать, хотя в таблице 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
просто игнорируется.