Триггер базы данных – это оформленный специальным образом именованный блок PL/SQL, хранящийся в базе данных. Каждый триггер связан с определенной таблицей и автоматически запускается ORACLE при выполнении одного из DML-операторов (INSERT, DELETE, UPDATE) или их совокупности над этой таблицей.
Назначение триггеров.Триггеры могут быть использованы:
1) для реализации сложных ограничений целостности данных, которые не могут быть осуществлены стандартным образом при создании таблицы;
2) предотвращения неверных транзакций;
3) выполнения процедур комплексной проверки прав доступа и секретности данных;
4) генерации некоторых выражений на основе значений, имеющихся в столбцах таблиц;
5) реализации сложных бизнес-правил для обработки данных (возможность отследить «эхо», т.е. возможность при изменении одной таблицы, обновлять данные связанных с ней таблиц).
Создание и включение триггеров.Для создания и автоматического включения триггера применяется следующий общий синтаксис:
CREATE [OR REPLACE] TRIGGER имя_триггера
{BEFORE | AFTER}
{INSERT | DELETE | UPDATE [OF список_столбцов]}
ON имя_таблицы [FOR EACH ROW] [WHEN условие]
< PL/SQL_блок >
При наличии ключевых слов OR REPLACE триггер создается заново, если он уже существует.
Конструкция BEFORE | AFTER указывает на момент запуска триггера. Вариант BEFORE означает, что триггер будет запускаться перед выполнением активизирующего DML-оператора; вариант AFTER означает, что триггер будет запускаться после выполнения активизирующего DML-оператора.
Конструкция INSERT | DELETE | UPDATE [OF список_столбцов] указывает тип активизирующего триггер DML-оператора. Разрешается, используя логическую операцию OR, задать совокупность активизирующих операторов, например: INSERT OR DELETE. Если при использовании варианта UPDATE указан список столбцов, то триггер будет запускаться при модификации одного из указанных столбцов; если список столбцов отсутствует, то триггер будет запускаться при изменении любого из столбцов связанной с триггером таблицы.
Конструкция FOR EACH ROW указывает на характер воздействия триггера: строковый или операторный. Если конструкция FOR EACH ROW присутствует, то триггер является строковым; при отсутствии ее триггер является операторным. Операторный триггер запускается один раз до или после выполнения активизирующего триггер DML-оператора независимо от того, сколько строк в связанной с триггером таблице подвергается модификации. Строковый триггер запускается один раз для каждой из строк, которая подвергается модификации DML-оператором, активизирующим триггер.
С помощью ключевого слова WHEN можно задать дополнительное ограничение на строки связанной с триггером таблицы, при модификации которых может быть запущен триггер.
Конструкция PL/SQL_блок представляет блок PL/SQL, который ORACLE запускает при активизации триггера.
Классификация триггеров.В основном различают двенадцатьтипов триггеров. Тип триггера определяется сочетанием следующих трех параметров:
1) характером воздействия триггера на строки связанной с ним таблицы (строковый или операторный);
2) моментом запуска триггера: до (BEFORE) или после (AFTER) исполнения активизирующего триггер DML-оператора;
Порядок активизации триггеров.Если у таблицы имеется несколько типов триггеров, то они активизируются по следующей схеме:
1) выполняется операторный триггер BEFORE (если их несколько, то ничего о порядке их выполнения сказать нельзя);
2) выполняется строковый триггер BEFORE;
3) выполняется активизирующий триггер DML-оператор с последующей проверкой всех ограничений целостности данных;
4) выполняется строковый триггер AFTER с последующей проверкой всех ограничений целостности данных;
5) выполняется операторный триггер AFTER.
Триггерные предикаты.Если в триггере указывается совокупность активизирующих триггер DML-операторов (например, INSERT OR DELETE), то для распознавания того, какой конкретно из DML-операторов выполняется над связанной с триггером таблицей, используются триггерные предикаты: INSERTING, DELETING, UPDATING. Они представляют собой логические функции, возвращающие TRUE, если тип активизирующего оператора совпадает с типом предиката, и FALSE – в противном случае. Для задания одних и тех же действий в случае выполнения различных DML-операторов в условном операторе триггерные предикаты объединяются с помощью логических операций.
Псевдозаписи.Для строковых триггеров существуют специальные конструкции, которые позволяют при выполнении DML-операторов над строкой таблицы, обращаться как к старым значениям, которые находились в ней до модификации, так и к новым, которые появятся в строке после ее модификации. Эти конструкции называются псевдозаписями и обозначаются old и new. Структура этих псевдозаписей идентична структуре строки модифицируемой таблицы, но оперировать можно только отдельными полями псевдозаписи. Обращение к полям псевдозаписи происходит по следующей схеме: перед old или new ставится символ «:», далее через точку указывается название поля. Значения, которые принимают поля псевдозаписи при выполнении активизирующих DML-операторов, определяются следующим образом.
Оператор INSERT – псевдозапись :new эквивалентна вставляемой строке, а псевдозапись :old во всех полях имеет значение NULL.
Оператор DELETE – псевдозапись :old эквивалентна удаляемой строке, а псевдозапись :new во всех полях имеет значение NULL.
Оператор UPDATE – псевдозапись :new эквивалентна строке, полученной в результате модификации, а псевдозапись :old во всех полях имеет исходное значение строки.
Включение, выключение триггеров.Хранящийся в базе данных триггер можно временно отключить, не удаляя его из базы данных. Для этого используется следующая команда:
ALTER TRIGGER имя_триггера DISABLE;
Включить триггер через некоторый промежуток времени можно, используя команду
ALTER TRIGGER имя_триггера ENABLE;
Запретить или разрешить запуск всех триггеров, связанных с некоторой таблицей, можно с помощью команды
ALTER TABLE имя_таблицы {DISABLE | ENABLE} ALL TRIGGERS;
где вариант DISABLE используется для отключения, а вариант ENABLE – для включения всех триггеров данной таблицы.
Удаление триггеров из базы данных.Уничтожение триггера, т. е. удаление триггера из базы данных осуществляется с помощью следующей команды:
DROP TRIGGER имя_триггера;
Получение информации о триггерах.Триггеры хранятся в базе данных, поэтому информацию о них можно получить из представления словаря данных USER_TRIGGERS, например, следующей командой:
SELECT * FROM USER_TRIGGERS;
Примеры.
1.Создать триггер, который перед вставкой очередной строки в таблицу KNIGA_POSTAVKA проверяет наличие указанного кодакниги в таблице KNIGA. При отсутствии указанного кода книги в таблице KNIGA должно генерироваться исключение с выдачей соответствующего сообщения.
Добавление новых строк в таблицу KNIGA_POSTAVKA выполняется оператором INSERT. Поскольку триггер должен запускаться перед выполнением каждого оператора INSERT, следовательно, он должен быть строковым BEFORE-триггером. Для сохранения целостности данных необходимо проверить, имеются ли вносимые коды книг и в таблице KNIGA. Для этого с помощью однострочного оператора SELECT осуществляется выборка информации из таблицы KNIGA, где в условии выборки используется поле КОД_КНИГИ псевдозаписи :new. Если количество строк с данным кодом книги в таблице KNIGA окажется равным нулю, будет сгенерировано исключение и выдано соответствующее сообщение.
Создание триггера TR1 выполняется вводом следующего оператора:
CREATE OR REPLACE TRIGGER TR1
BEFORE INSERT ON KNIGA_POSTAVKA
FOR EACH ROW
DECLARE
KOL NUMBER(4);
BEGIN
SELECT COUNT(*) INTO KOL FROM KNIGA
WHERE КОД_КНИГИ = :NEW.КОД_КНИГИ;
IF KOL = 0 THEN RAISE_APPLICATION_ERROR
(–20212,'В таблице KNIGA нет информации о данной книге');
END IF;
END TR1;
Действие триггера TR1 может быть проверено выполнением следующего оператора, осуществляющего вставку строки в таблицу KNIGA_POSTAVKA и тем самым вызывающих активизацию триггера TR2:
INSERT INTO KNIGA_POSTAVKA VALUES(21,15,’Иванов’,15,
’20-JAN-2006’);
Поскольку код книги 15 отсутствует в таблице KNIGA, то будет сгенерировано исключение и выдано соответствующее сообщение.
2.Создать триггер, который запрещает вносить в таблицу KNIGA строки со значением поля ЦЕНА больше, чем 5000 рублей, а также осуществлять увеличение цены книг, информация о которых хранится в таблице KNIGA, более чем на 20 %. При нарушении данного требования должно генерироваться исключение с выдачей соответствующего сообщения.
Так как внесение новых строк в таблицу KNIGA осуществляется в результате выполнения оператора INSERT, а значение поля ЦЕНА в таблице KNIGA, содержащего цену книги, может быть изменено в результате выполнения оператора UPDATE, то в триггере указывается совокупность запускающих DML-операторов. Поскольку триггер должен запускаться перед выполнением каждого из указанных DML-операторов, следовательно, он является строковым BEFORE-триггером. Так как действия, выполняемые триггером, различны для каждого из запускающих DML-операторов, модифицирующих таблицу KNIGA, то для распознавания типа DML-оператора используются соответствующие триггерные предикаты INSERTING и UPDAITING. Вследствие того что при вставке новых строк проверке должно быть подвергнуто новое значение поля ЦЕНА, а при модификации значения поля ЦЕНА новое значение должно сравниваться со старым значением, необходимо использовать псевдозаписи :new и :old.
Создание триггера TR2 выполняется вводом следующего оператора:
CREATE OR REPLACE TRIGGER TR2
BEFORE INSERT OR UPDATE OF ЦЕНА ON KNIGA
FOR EACH ROW
BEGIN
IF INSERTING THEN
IF :NEW.ЦЕНА > 5000 THEN
RAISE_APPLICATION_ERROR
(–20102, 'В таблицу KNIGA нельзя вносить записи с ценой книги > 5000');
END IF;
END IF;
IF UPDATING THEN
IF :NEW.ЦЕНА > :OLD.ЦЕНА*1.2 THEN
RAISE_APPLICATION_ERROR
(–20103, 'В таблице KNIGA нельзя изменять цену книги более чем на 20 %');
END IF;
END IF;
END TR2;
Действие триггера TR2 может быть проверено выполнением следующих операторов, которые, осуществляя вставку строк в таблицу KNIGA и обновление строк в таблице KNIGA, тем самым вызывают его активизацию.
Оператор вставки строк в таблицу KNIGA, вызывающие активизацию триггера TR2:
INSERT INTO KNIGA VALUES( 21, 'Дюна', 'Герберт', 5268, 'Аст',
'Фантастика');
Оператор обновления строк в таблице KNIGA, вызывающие активизацию триггера TR2:
UPDATE KNIGA SET ЦЕНА=6000;
Поскольку эти операторы нарушают требования, предъявляемые к значению и модификации цены книг, то во всех случаях будет сгенерировано исключение и выдано соответствующее сообщение.
3.Создать триггер, который в созданную таблицу STAT, содержащую столбцы:
имя издательства – IZD,
количество книг в жанре «Роман» – KOL_ROM,
количество книг в жанре «Фантастика» – KOL_FAN,
при каждой модификации таблицы KNIGA формирует и заносит в соответствующие столбцы таблицы STAT суммарное количество книг по каждому из издательств в разрезе указанных тематик: «Роман» и «Фантастика».
Модификация таблицы KNIGA осуществляется выполнением следующих DML-операторов: INSERT, DELETE или оператора UPDATE, модифицирующего значения столбца ЖАНР в таблице KNIGA. Так как действия по формированию информации таблицы STAT выполняются после выполнения каждого из модифицирующих таблицу KNIGA операторов, то по типу это операторный AFTER-триггер. Поскольку действия, выполняемые триггером, одинаковы для всех типов активизирующих его операторов, то триггерные предикаты не используются. Перед созданием триггера должна быть создана таблица STAT.
Создание таблицы STAT может быть выполнено вводом следующей совокупности операторов:
DROP TABLE STAT;
CREATE TABLE STAT
( IZD VARCHAR2(15),
KOL_ROM NUMBER(7),
KOL_FAN NUMBER(7)
);
Создание триггера TR3 выполняется вводом следующего оператора:
CREATE OR REPLACE TRIGGER TR3
AFTER INSERT OR DELETE OR UPDATE OF ЖАНР
ON KNIGA
DECLARE
CURSOR V1 IS SELECT ИЗДАТЕЛЬСТВО,
COUNT(НАЗВАНИЕ) KOL1
FROM KNIGA WHERE ЖАНР = 'Роман'
GROUP BY ИЗДАТЕЛЬСТВО;
CURSOR V2 IS SELECT ИЗДАТЕЛЬСТВО,
COUNT(НАЗВАНИЕ) KOL2
FROM KNIGA WHERE ЖАНР = 'Фантастика'
GROUP BY ИЗДАТЕЛЬСТВО;
BEGIN
DELETE FROM STAT;
FOR Z1 IN V1 LOOP
INSERT INTO STAT VALUES(Z1.ИЗДАТЕЛЬСТВО,
Z1.KOL1, 0);
END LOOP;
FOR Z1 IN V2 LOOP
UPDATE STAT SET KOL_FAN = Z1.KOL2
WHERE IZD = Z1.ИЗДАТЕЛЬСТВО;
IF SQL%NOTFOUND THEN
INSERT INTO STAT VALUES(Z1.ИЗДАТЕЛЬСТВО, 0,
Z1.KOL2);
END IF;
END LOOP;
END TR3;
Действие триггера может быть проверено выполнением следующих операторов, которые, осуществляя вставку строк в таблицу KNIGA, удаление строк и обновление строк в таблице KNIGA, тем самым вызывают активизацию триггера TR3.
Операторы вставки строк в таблицу KNIGA, вызывающие активизацию триггера TR3:
INSERT INTO KNIGA VALUES( 46, 'Еретики Дюны', 'Герберт',368,
'Аст', 'Фантастика');
INSERT INTO KNIGA VALUES( 42, 'Ингвар и Ольха',
'Никитин',168, 'Аст', 'Роман ');
Операторы удаления строк из таблицы KNIGA, вызывающие активизацию триггера TR3:
DELETE KNIGA WHERE АВТОР = 'Герберт';
DELETE KNIGA WHERE НАЗВАНИЕ = 'Казаки';
Операторы модификации строк в таблице KNIGA, вызывающие активизацию триггера TR3:
UPDATE KNIGA SET ЖАНР='Фантастика' WHERE НАЗВАНИЕ =
'Ингвар и Ольха';
Просмотр информации в таблице STAT можно выполнить следующим оператором:
