Создание триггеров PL/SQL
Триггер базы данных (database trigger) является объектом реляционной базы данных, который активизирует выполнение хранимой (или встроенной) PL/SQL-процедуры при изменении пользователем данных в таблице. Событие, управляющее запуском триггера, описывается в виде логических условий. Например, попытка модифицировать данные в таблице активизирует триггер, соответствующий данной команде манипулирования данными. Число триггеров на таблицу базы данных не ограничено.
Обычно триггеры используют для реализации ограничений ссылочной целостности, для предотвращения несогласованных изменений в базе данных (поддержка целостности базы данных), для выполнения скрытых операций при модификации, а также для снижения сетевого трафика за счет передачи обработки на сервер. Операции завершения транзакции выполняются после обработки триггеров.
При выполнении команды UPDATE с помощью триггера можно проверить, что модифицируемые данные удовлетворяют ограничениям целостности базы данных до выполнения операции (при этом возможен доступ к новым данным!). После выполнения операции с помощью триггера можно выполнить скрытую обработку данных с учетом поступивших изменений (старые данные также могут быть доступны).
При выполнении команды INSERT также можно проверить данные до вставки в таблицу на допустимость ограничениям целостности, а после - выполнить операции над только что вставленными данными.
При выполнении команды DELETE можно проверить данные до их удаления или восстановить данные после удаления.
Для создания триггера предусмотрена специальная команда SQL CREATE TPIGGER. Эта команда создает триггер на таблице, которой владеет пользователь. Невозможно создать триггер для виртуальной таблицы.
Синтаксис команды следующий:
CREATE [OR REPLACE] TPIGGER [имя схемы.]имя триггера {BEFORE|AFTER} {INSERT|DELETE|UPDATE [OF имя колонки [, имя колонки ѕ]]} [OR {INSERT|DELETE|UPDATE [OF имя колонки [, имя колонки ѕ]]}] ON [имя схемы.]{имя таблицы|имя представления} {FOR EACH ROW][WHEN условие] спецификация пакета на PL/SQL
Ключевое слово OR REPLACE указывает на безусловное замещение старого теста триггера. Если оно не указывается, а триггер определен в базе данных, то замещения старого триггера не происходит, и возвращается сообщение об ошибке.
Определение триггера состоит из нескольких частей:
- задание имени триггера;
- указание команды SQL, к которой относится триггер;
- указание таблицы или представления, для которой определяется триггер;
- задание ограничений триггера;
- задание действия в теле триггера.
Если имя схемы опущено, то триггер создается в схеме текущего пользователя.
Первая часть - это указание команды, которая запускает триггер. При создании триггера необходимо указывать, к какой команде манипулирования данными он относится - INSERT, DELETE или UPDATE. Для последней модно указывать конкретные колонки, указав фразу OF имя_колонки [, имя_колонки ...] в предложении UPDATE.
Ключевое слово ON задает имя таблицы или представления, для которого создается триггер.
Ограничения триггера указывают дополнительные условия, которые должны удовлетворяться для строки, чтобы триггер запустился.
Триггер имеет время действия - до или после выполнения команды манипулирования данными, - которое задается ключевыми словами BEFORE или AFTER, соответственно. Обычно нельзя определить два триггера на одну и ту же операцию с одинаковым временем действия.
Необязательное ключевое слово ON EACH ROW определяет триггер как строчный, т.е. запускаемый для каждой строки результирующего множества команды SQL. Если оно опущено, то триггер запускается только один раз в начале обработки команды. Таким образом, условие "для каждой строки" активизируется, только когда есть строки (например, предложение WHERE дает истинное значение условий поиска), в то время как для условия "для каждой команды" триггер сработает и в этом случае.
Дополнительные условия, сужающие область действия триггера, могут быть заданы в предложении WHEN. Условия, задаваемые в этом предложении, являются стандартными для SQL условиями, должны содержать корреляционные имена и не могут содержать запрос.
Это предложение может быть указано только для строчного триггера.
Действие, которое выполняет триггер, задается в теле триггера блоком кода PL/SQL, который не может содержать команд управления транзакциями, таких как COMMIT, ROLLBACK и SAVEPOINT.
Для того чтобы задать корреляционные имена, т.е. чтобы видеть старые и новые значения колонок при обновлении, нужно воспользоваться предложением REFERENCING OLD AS имя_таблицы_старых_значений NEW AS имя_таблицы_новых_значений. Определяемые имена являются псевдонимами для обновляемой таблицы и должны быть различны. На эти имена можно ссылаться в теле триггера. По умолчанию корреляционные имена есть OLD и NEW для старого и нового значения строки.
С каждым триггером можно связать его тип. Тип триггера определяется следующими условиями:
- когда запускается триггер (т.е. какое у него время действия);
- сколько раз запускается триггер.
Таким образом, тип триггера определяется всевозможными комбинациями ключевых слов BEFORE, AFTER и FOR EACH ROW, что дает четыре основных типа триггера, как показано в таблице 12.3 ниже.
| BEFORE | BEFORE: СУБД запускает триггер до выполнения команд | BEFORE: СУБД запускает триггер до модификации каждой строки, обрабатываемой командой |
| AFTER | AFTER: СУБД запускает триггер после выполнения команды | AFTER: СУБД запускает триггер после модификации каждой строки, обрабатываемой командой |
Пример
CREATE TRIGGER emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON employee DECLARE dummy INTEGER; BEGIN /* Если сегодня суббота или воскресенье, то ошибка. */ IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR TO_CHAR(SYSDATE, 'DY') = 'SUN') THEN raise_application_error( -20501, 'Можно изменять таблицу employee только в рабочие дни'); END IF; /* Если праздник, то тоже ошибка */ SELECT COUNT(*) INTO dummy FROM company_holidays WHERE day = TRUNC(SYSDATE); IF dummy > 0 THEN raise_application_error( -20501, 'Нельзя изменять таблицу employee по праздникам'); END IF; /* Если текущее время меньше 8:00 часов утра или 5:00 часов вечера, то ошибка */ IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR TO_CHAR(SYSDATE, 'HH24') >= 17) THEN raise_application_error( -20502, 'Нельзя изменять таблицу employee в нерабочие часы'); END IF; END;
