• Тип предложения SQL, которое возбуждает тело триггера. Допустимыми возможностями являются DELETE, INSERT и UPDATE. В спецификацию предложения триггера могут быть включены одна, две или все три этих опции.
• Таблицу, ассоциированную с триггером. Заметьте, что в предложении триггера может быть специфицирована ровно одна таблица (но не обзор).

Например, предложениями, возбуждающими триггер DUMMY, являются любые предложения DELETE, INSERT или UPDATE по таблице EMP. Любое из следующих предложений возбудит триггер DUMMY, показанный в предыдущем примере:

DELETE FROM emp;
INSERT INTO emp VALUES ( . . . );
INSERT INTO emp SELECT . . . FROM . . . ;
UPDATE emp SET . . . ;

Список столбцов для UPDATE

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

Предыдущий пример определения триггера DUMMY мог бы содержать список столбцов для предложения триггера, например:

. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .

Присутствие или отсутствие опции FOR EACH ROW определяет, является ли этот триггер триггером предложения или триггером строки. Если эта опция включена, она указывает, что тело триггера возбуждается отдельно для каждой строки таблицы, затрагиваемой предложением триггера. Отсутствие опции FOR EACH ROW указывает, что данный триггер должен возбуждаться лишь один раз для предложения триггера.

В определение триггера строки может быть включено необязательное ограничение триггера, путем специфицирования булевского выражения SQL в фразе WHEN (фраза WHEN не может быть включена в определение триггера предложения). Выражение в фразе WHEN, если эта фраза присутствует, вычисляется для каждой строки, затрагиваемой триггером. Если результат выражения дает TRUE для строки, то тело триггера исполняется для этой строки. Однако, если это выражение вычисляется для строки как FALSE или NULL, то тело триггера не исполняется для этой строки. Вычисление условия фразы WHEN не влияет на исполнение самого предложения триггера (т.е. предложение триггера НЕ откатывается, если выражение в фразе WHERE вычисляется как FALSE).

Например, для триггера DUMMY, тело триггера не исполняется, если новое значение столбца EMPNO нулевое, отрицательное или NULL. В более реалистичных примерах вы могли бы проверять, скажем, чтобы одно значение столбца было меньше другого.

Выражение в фразе WHEN для триггера строки может включать корреляционные имена, которые объясняются ниже. Выражение в фразе WHEN должно быть выражением SQL (но не выражением PL/SQL), и не может включать подзапрос.

Тело триггера - это блок PL/SQL, который может содержать предложения SQL и PL/SQL. Эти предложения исполняются тогда, когда выдано предложение триггера, и ограничение триггера (если оно есть) вычислено как TRUE. Для триггеров строк тело триггера имеет некоторые специальные конструкты, которые могут быть включены в код этого блока PL/SQL: корреляционные имена, опцию REFERENCING, а также условные предикаты INSERTING, DELETING и UPDATING.

Доступ к значениям столбцов в триггерах строки

Внутри тела триггера строк, код PL/SQL и предложения SQL имеют доступ как к старым, так и к новым значениям столбцов текущей строки, затрагиваемой предложением триггера. Для каждого столбца модифицируемой таблицы определены два КОРРЕЛЯЦИОННЫХ ИМЕНИ: одно для старого (old), другое - для нового значения столбца (new). В зависимости от типа предложения триггера, то или иное корреляционное имя может быть лишено смысла.

• Триггер, возбужденный предложением INSERT, имеет осмысленный доступ лишь к новым значениям столбцов. Поскольку строка создается предложением INSERT, старые значения столбцов пусты (NULL).
• Триггер, возбужденный предложением UPDATE, имеет доступ как к старым, так и к новым значениям столбцов для обоих возможных типов триггера (BEFORE или AFTER).
• Триггер, возбужденный предложением DELETE, имеет осмысленный доступ лишь к старым значениям столбцов. Поскольку строка перестает существовать после ее удаления, новые значения столбцов пусты (NULL).

Новые значения столбцов адресуются квалификатором NEW перед именем столбца, старые - квалификатором OLD. Например, если предложение триггера ассоциировано с таблицей EMP, содержащей столбцы SAL, COMM и т.д., то вы можете включить в тело триггера предложения, подобные следующим:

IF :new.sal > 10000 . . .
IF :new.sal < :old.sal . . .

Старые и новые значения доступны как в триггерах BEFORE, так и в триггерах AFTER. Назначать новое значение столбца можно в триггере строк BEFORE, но не в триггере строк AFTER (потому что предложение триггера уже выполнено, прежде чем триггер AFTER получает управление). Если триггер строк BEFORE изменяет значение NEW для столбца, то триггер AFTER, возбужденный тем же самым предложением, видит значение, которое было назначено триггером BEFORE.

Корреляционные имена могут также использоваться в булевском выражении фразы WHEN. Заметьте, что перед квалификаторами OLD и NEW должно кодироваться двоеточие, когда они используются в теле триггера, но двоеточие не допускается, когда эти квалификаторы используются в фразе WHEN или опции REFERENCING.

Опция REFERENCING может специфицироваться в теле триггера строк для того, чтобы избежать конфликтов между корреляционными именами и именами таблиц, в случае, если таблица имеет имя "OLD" или "NEW". Поскольку такая ситуация редка, эта опция почти никогда не применяется.

Например, предположим, что у вас есть таблица с именем NEW, содержащая столбцы FIELD1 (числовой) и FIELD2 (символьный). Следующее предложение CREATE TRIGGER показывает триггер, ассоциированный с таблицей NEW, который использует опцию REFERENCING, чтобы избежать конфликтов между корреляционными именами и именем таблицы:

CREATE TRIGGER dummy
BEFORE UPDATE ON new
REFERENCING new AS newest
FOR EACH ROW
BEGIN
:newest.field2 := TO_CHAR (:newest.field1);
END;

Заметьте, как квалификатор NEW переименован в NEWEST с помощью опции REFERENCING, а затем использован в теле триггера.

Если триггер может быть возбужден более чем одним типом предложения DML (например, "INSERT OR DELETE OR UPDATE OF emp"), то в теле триггера можно использовать условные предикаты INSERTING, DELETING и UPDATING, для того чтобы выполнять различные участки кода в зависимости от типа предложения, возбудившего триггер. Предположим, что предложение триггера определено следующим образом:

INSERT OR UPDATE ON emp

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

IF INSERTING THEN . . . END IF;
IF UPDATING THEN . . . END IF;

Первое условие будет вычисляться как TRUE лишь в тех случаях, когда триггер был возбужден предложением INSERT; второе условие будет вычисляться как TRUE лишь в тех случаях, когда триггер был возбужден предложением UPDATE.

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

CREATE TRIGGER . . . . . . UPDATE OF sal, comm ON emp . . . BEGIN . . . IF UPDATING ('SAL') THEN . . . END IF; END;

Код в фразе THEN выполняется лишь в том случае, если предложение UPDATE, возбудившее триггер, обновляет столбец SAL. Например, следующее предложение возбудит показанный выше триггер и заставит условный предикат вычислиться как TRUE:

UPDATE emp SET sal = sal + 100;

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

Для дополнительной информации об обработке ошибок в программных единицах PL/SQL обратитесь к секциям "Обработка ошибок" и "Объявление исключений и программы обработки исключений" на страницах 7-11 и 7-12.

Следующие секции обсуждают ограничения, которые налагаются на триггеры.

ЧИСЛО ТРИГГЕРОВ НА ТАБЛИЦУ. Лишь один триггер каждого типа может существовать на таблицу. Это позволяет иметь для таблицы двенадцать возможных триггеров:

BEFORE UPDATE строк AFTER UPDATE строк BEFORE DELETE строк AFTER DELETE строк BEFORE INSERT предложения AFTER INSERT предложения BEFORE INSERT строк AFTER INSERT строк BEFORE UPDATE предложения AFTER UPDATE предложения BEFORE DELETE предложения AFTER DELETE предложения

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

Замечание: Каждая таблица может иметь до четырех триггеров UPDATE (BEFORE/AFTER, предложения/строки), независимо от того, возбуждаются ли эти триггеры при обновлении специфических столбцов.

ДОПУСТИМЫЕ ПРЕДЛОЖЕНИЯ SQL В ТЕЛЕ ТРИГГЕРА. Тело триггера может содержать любые предложения DML, включая предложения SELECT (только SELECT ... INTO или предложения SELECT в определениях курсоров), INSERT, UPDATE и DELETE; в теле триггера не допускаются предложения DDL. Нельзя также управлять транзакциями в контексте триггера. Поэтому внутри контекста тела триггера не допускаются следующие предложения: ROLLBACK, COMMIT и SAVEPOINT.

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

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

ТИПЫ ДАННЫХ LONG И LONG RAW В ТРИГГЕРЕ. Следующие правила объясняют особенности использования типов данных LONG и LONG RAW внутри триггеров:

• Предложение SQL внутри триггера может вставлять данные в столбец типа данных LONG или LONG RAW.
• Если данные из столбца LONG или LONG RAW могут быть преобразованы в ограниченный тип данных (такой как CHAR или VARCHAR2), то к такому столбцу LONG или LONG RAW можно обращаться в предложении SQL внутри триггера. Заметьте, что максимальная длина для этих типов данных равна 32K.
• Нельзя объявлять переменные с типом данных LONG или LONG RAW.
• Нельзя использовать квалификаторы :NEW и :OLD со столбцами LONG и LONG RAW.

ТАБЛИЦЫ С ЖУРНАЛАМИ СНИМКОВ. Создание журнала снимков неявно создает триггер AFTER ROW по главной таблице. Поэтому нельзя создать пользовательский триггер AFTER ROW по главной таблице, имеющей журнал снимков. И наоборот, если для таблицы определен триггер AFTER ROW, то для этой таблицы нельзя создать журнал снимков; необходимо сначала удалить этот триггер. Снимки и журналы снимков обсуждаются в главе 12.

ССЫЛКИ НА ПАКЕТИРОВАННЫЕ ПЕРЕМЕННЫЕ. Если предложение UPDATE или DELETE обнаруживает конфликт с конкурирующей операцией UPDATE, то ORACLE выполняет прозрачный откат к точке сохранения и повторно запускает обновление. Это может повториться многократно, пока предложение не выполнится успешно. Каждый раз, когда предложение перезапускается, ассоциированный триггер BEFORE предложения снова возбуждается. Откат к точке сохранения не отменяет изменений в любых пакетированных переменных, адресуемых в этом триггере. Соответствующий пакет должен включать переменную-счетчик, чтобы распознавать такую ситуацию.

ПОРЯДОК ВЫЧИСЛЕНИЯ СТРОК. Реляционная база данных не гарантирует определенности порядка строк, обрабатываемых предложением SQL. Поэтому не создавайте триггеров, которые зависят от того, в каком порядке будут обрабатываться строки. Например, не назначайте значения глобальной пакетированной переменной в триггере строк, если это текущее значение зависит от того, какая строка сейчас обрабатывается триггером строк. Кроме того, если в триггере обновляются глобальные пакетированные переменные, лучше всего инициализировать такие переменные в триггере предложения BEFORE.

Если предложение в теле триггера вызывает возбуждение другого триггера, этот эффект называется КАСКАДОМ ТРИГГЕРОВ. ORACLE позволяет иметь одновременно до 32 каскадно возбужденных триггеров. Однако вы можете по существу ограничить количество каскадных триггеров через параметр инициализации MAX_OPEN_CURSORS; для каждого исполнения триггера должен открываться курсор.

МУТИРУЮЩИЕ И ОГРАНИЧИВАЮЩИЕ ТАБЛИЦЫ. Мутирующая таблица - это таблица, модифицируемая в данный момент предложением UPDATE, DELETE или INSERT, или таблица, которая может потребовать обновления в результате действия декларативного ссылочного ограничения целостности DELETE CASCADE. Ограничивающая таблица - это таблица, которую предложение, возбуждающее триггер или ограничение, должно читать, - либо явно (для предложения SQL), либо неявно (для декларативного ограничения ссылочной целостности). Мутирующая или ограничивающая таблица является таковой лишь для той сессии, которая выдает предложение, возбуждающее триггер или ограничение.

Для триггеров предложений таблица не рассматривается как мутирующая или как ограничивающая; однако для триггеров строк имеют место два важных ограничения, касающихся мутирующих и ограничивающих таблиц:

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

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

Рис.8-1 иллюстрирует ограничение для мутирующих таблиц.

Рис.8-1. Мутирующие таблицы

Пример, показанный на рис.8-1, работает следующим образом. Предложение SQL исполняется для первой строки таблицы. Затем возбуждается триггер AFTER ROW. В свою очередь, предложение внутри тела триггера AFTER ROW пытается опросить первоначальную таблицу. Однако, поскольку таблица EMP мутирует, ORACLE не позволяет этот запрос. Поэтому возникает ошибка времени выполнения, эффекты тела триггера и предложения, возбудившего триггер, откатываются, и управление возвращается пользователю или приложению.

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

Декларативные ограничения целостности проверяются в различные моменты времени по отношению к триггерам строк; обратитесь к документу ORACLE7 Server Concepts Manual за информацией о том, как взаимодействуют триггеры и ограничения целостности.

Чтобы создать триггер в своей схеме, вы должны иметь системную привилегию CREATE TRIGGER, а также одно из:

• владеть таблицей, специфицированной в предложении триггера, или
• иметь привилегию ALTER для таблицы, специфицированной в предложении триггера, или
• иметь системную привилегию ALTER ANY TABLE

Чтобы создать триггер в схеме другого пользователя, вы должны иметь системную привилегию CREATE ANY TRIGGER. Эта привилегия позволять создать триггер в любой схеме и ассоциировать его с таблицей любого пользователя.

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

Триггеры можно отлаживать с помощью тех же средств, которые применяются для отладки процедур и пакетов; см. секцию "Отладка процедур и пакетов" на странице 7-15.

Нельзя явно изменить триггер; его необходимо заменить новым определением триггера. Если вы используете текстовый редактор для создания ваших триггеров, вы можете просто отредактировать соответствующий текстовый файл и выполнить предложение CREATE TRIGGER, чтобы определить новую версию кода.

Заменяя триггер, вы должны включить в предложение CREATE TRIGGER опцию OR REPLACE. Опция OR REPLACE позволяет заменить существующий триггер новой версией, не затрагивая никаких грантов, которые были выданы для первоначальной версии этого триггера.

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

Для удаления триггера из базы данных используйте команду DROP TRIGGER. Например, чтобы удалить триггер с именем REORDER, введите следующее предложение:

DROP TRIGGER reorder;

Чтобы удалить триггер, вы должны иметь его в своей схеме, либо иметь системную привилегию DROP ANY TRIGGER.

Триггер может находиться в одном из двух различных режимов:

Вы можете временно выключить триггер, если имеет место одно из следующих условий:

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

По умолчанию, триггер включается в момент его создания. Чтобы отключить триггер, используйте команду ALTER TRIGGER с опцией DISABLE. Например, следующее предложение отключает триггер REORDER по таблице INVENTORY:

ALTER TRIGGER reorder DISABLE;

Вы можете одновременно отключить все триггеры, ассоциированные с таблицей, с помощью команды ALTER TABLE с опциями DISABLE и ALL TRIGGERS. Например, следующее предложение отключает все триггеры, определенные для таблицы INVENTORY:

ALTER TABLE inventory
DISABLE ALL TRIGGERS;

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

Чтобы включить триггер, используйте команду ALTER TRIGGER с опцией ENABLE. Например, следующее предложение включает триггер REORDER по таблице INVENTORY:

ALTER TRIGGER reorder ENABLE;

Вы можете одновременно включить все триггеры, ассоциированные с таблицей, с помощью команды ALTER TABLE с опциями ENABLE и ALL TRIGGERS. Например, следующее предложение включает все триггеры, определенные для таблицы INVENTORY:

ALTER TABLE inventory
ENABLE ALL TRIGGERS;

Для включения и выключения триггеров с помощью команды ALTER TABLE, вы должны либо владеть таблицей, либо иметь объектную привилегию ALTER TABLE для таблицы или системную привилегию ALTER ANY TABLE. Для включения или выключения индивидуального триггера с помощью команды ALTER TRIGGER, вы должны либо владеть триггером, либо иметь системную привилегию ALTER ANY TRIGGER.

Следующие обзоры словаря данных раскрывают информацию о триггерах:

• USER_TRIGGERS
• ALL_TRIGGERS
• DBA_TRIGGERS

Полное описание этих обзоров словаря данных приведено в приложении D.

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

CREATE TRIGGER reorder
AFTER UPDATE OF parts_on_hand ON inventory
FOR EACH ROW
WHEN (new.parts_on_hand < new.reorder_point)
DECLARE
x NUMBER;
BEGIN
SELECT COUNT(*) INTO x
FROM pending_orders
WHERE part_no = :new.part_no;
IF x = 0 THEN
INSERT INTO pending_orders
VALUES (:new.part_no, :new.reorder_quantity, sysdate);
END IF;
END;

Следующие два запроса возвращают информацию о триггере REORDER:

SELECT type, triggering_statement, table_name
FROM user_triggers
WHERE name = 'REORDER';

TYPE             TRIGGERING_STATEMENT       TABLE_NAME
---------------- -------------------------- ------------
AFTER EACH ROW   UPDATE                     INVENTORY

SELECT trigger_body
FROM user_triggers
WHERE name = 'REORDER';

TRIGGER_BODY
--------------------------------------------
DECLARE
x NUMBER;
BEGIN
SELECT COUNT(*) INTO x
FROM pending_orders
WHERE part_no = :new.part_no;
IF x = 0 THEN
INSERT INTO pending_orders
VALUES (:new.part_no, :new.reorder_quantity, sysdate);
END IF;
END;

Вы можете использовать триггеры разнообразными способами, чтобы привязать к своим нуждам управление данными в базе данных ORACLE. Например, триггеры обычно используются для:

• изощренного аудитинга
• предотвращения незаконных транзакций
• обеспечения ссылочной целостности между узлами в распределенной базе данных
• реализации сложных организационных правил
• ввода в действие комплексных правил защиты
• прозрачной регистрации событий
• автоматической генерации значений вычисляемых столбцов
• поддержания синхронных дублирований таблиц

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

Иногда команда ORACLE AUDIT рассматривается как средство аудита ЗАЩИТЫ, тогда как триггеры могут обеспечить средства ФИНАНСОВОГО аудита.

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

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

Использовать ли триггеры строк или триггеры предложений, зависит от того, какую информацию вы отслеживаете. Например, триггеры строк обеспечивают отслеживание значений по строкам таблицы. Триггеры могут также позволить пользователю предоставлять "код причины", по которой выдается отслеживаемое предолжение, что может быть полезным при аудитинге как на уровне строк, так и на уровне предложений.

Следующий триггер отслеживает модификации таблицы EMP по строкам. Он требует, чтобы в глобальной переменной пакета перед обновлением был предоставлен "код причины".

Этот триггер демонстрирует:

• как триггеры могут обеспечить аудитинг по значениям
• как можно применять общие пакетированные переменные

Комментарии в коде объясняют логику триггера.

CREATE TRIGGER audit_employee
AFTER INSERT OR DELETE OR UPDATE ON emp
FOR EACH ROW
BEGIN
/*  AUDITPACKAGE - это пакет, в котором объявлена общая
переменная REASON. Эта переменная должна быть установлена
приложением с помощью команды, например, такой как
EXECUTE AUDITPACKAGE.SET_REASON(reason_string). Заметьте,
что пакетированная переменная сохраняет свое значение на
протяжении всей сессии, и что каждая сессия имеет свою
собственную копию всех пакетированных переменных. */

IF auditpackage.reason IS NULL THEN
raise_application_error(-20201,
'Задайте причину через AUDITPACKAGE.SET_REASON(reason_string)');
END IF;

/*  Если выполнено приведенное выше условие, т.е. переменная
REASON пуста, то выдаются указанное сообщение и код
ошибки, выполнение триггера прекращается, и результаты
предложения, возбудившего триггер, откатываются.
В противном случае, триггер вставляет новую строку в
предопределенную

аудиторскую таблицу AUDIT_EMPLOYEE,
записывая старое и новое значения в таблице EMP и код
причины, определенный значением переменной REASON из пакета
AUDITPACKAGE. Заметьте, что "старые" значения пусты, если
триггер вызван предложением INSERT, и "новые" значения
пусты, если триггер вызван предложением DELETE. */

INSERT INTO audit_employee VALUES
(:old.ssn, :old.name, :old.job_classification, :old.sal,
:new.ssn, :new.name, :new.job_classification, :new.sal,
auditpackage.reason, user, sysdate);
END;

Если угодно, вы можете также сбрасывать переменную REASON в пустое значение, чтобы заставить пользователей устанавливать код причины перед каждым обновлением. Следующий простой триггер предложения с условием AFTER сбрасывает код причины после выполнения предложения триггера:

CREATE TRIGGER audit_employee_reset
AFTER INSERT OR DELETE OR UPDATE ON emp
BEGIN
auditpackage.set_reason(NULL);
END;

Заметьте, что оба показанных выше триггера возбуждаются одним и тем же типом предложений SQL. Однако, первый триггер (триггер AFTER уровня строки) возбуждается один раз для каждой строки таблицы, затрагиваемой предложением триггера, тогда как второй триггер (триггер AFTER уровня предложения) возбуждается один раз после того, как закончено выполнение предложения триггера.

Ниже показан еще один пример применения триггеров для аудита. Этот триггер отслеживает изменения, которые вносятся в таблицу EMP, и записывает эту информацию в таблицы AUDIT_TABLE и AUDIT_TABLE_VALUES.

CREATE OR REPLACE TRIGGER audit_emp
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
time_now DATE;
terminal CHAR(10);


BEGIN
time_now := SYSDATE;                  -- текущее время
terminal := USERENV('TERMINAL');      -- терминал пользователя
IF INSERTING THEN             -- записать первичный ключ
INSERT INTO audit_table     -- нового сотрудника
VALUES (audit_seq.NEXTVAL, user, time_now, terminal,
'EMP', 'INSERT', :new.empno);
ELSIF DELETING THEN           -- записать первичный ключ

INSERT INTO audit_table     -- удаляемого сотрудника
VALUES (audit_seq.NEXTVAL, user, time_now, terminal,
'EMP', 'DELETE', :old.empno);
ELSE                          -- записать первичный ключ
INSERT INTO audit_table     -- обновляемой строки
VALUES (audit_seq.NEXTVAL, user, time_now, terminal,
'EMP', 'UPDATE', :old.empno);
-- для столбцов SAL, DEPTNO записать старые и новые значения
IF UPDATING ('SAL') THEN
INSERT INTO audit_table_values
VALUES (audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal);
ELSIF UPDATING ('DEPTNO') THEN
INSERT INTO audit_table_values
VALUES (audit_seq.CURRVAL, 'DEPTNO', :old.deptno,
:new.deptno);
END IF;
END IF;
END;
/

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

Декларативное ограничение целостности - это утверждение о базе данных, которое всегда истинно. Ограничение применяется к существующим данным в таблице и к любому предложению, которое манипулирует этой таблицей; для дополнительной информации обратитесь к главе 6.

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

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

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

• чтобы задействовать ссылочную целостность, когда нужное правило ссылочной целостности не может быть введено в действие через ограничения целостности: обновление CASCADE, обновление и удаление SET NULL, обновление и удаление SET DEFAULT
• чтобы задействовать ссылочную целостность, когда зависимая и родительская таблицы находятся на разных узлах распределенной базы данных
• чтобы задействовать комплексные организационные правила, которые не могут быть определены через выражения, допустимые в ограничениях CHECK

Реализация ссылочной целостности с помощью триггеров

С помощью триггеров могут быть введены в действие многие варианты ссылочной целостности. Однако применяйте триггеры лишь тогда, когда вы хотите реализовать ссылочные действия UPDATE и DELETE SET NULL, UPDATE и DELETE SET DEFAULT, или если вы хотите задействовать ссылочную целостность, когда зависимая и родительская таблицы находятся на разных узлах распределенной базы данных.

Применяя триггеры для поддержания ссылочной целостности, объявите ограничение PRIMARY KEY или UNIQUE по родительской таблице. Если ссылочная целостность должна поддерживаться между родительской и порожденной таблицами в одной и той же базе данных, то вы можете также объявить внешний ключ в порожденной таблице, но отключить его; это предотвратит возможность удаления соответствующего ограничения PRIMARY KEY (если только ограничение PRIMARY KEY не удаляется явно опцией CASCADE).

Чтобы поддерживать ссылочное ограничение с помощью триггеров:

• Для порожденной таблицы должен быть определен триггер, гарантирующий, что значения, вставляемые или обновляемые во внешнем ключе, соответствуют значениям родительского ключа.
• Один или несколько триггеров должны быть определены для родительской таблицы. Эти триггеры гарантируют выполнение нужного ссылочного действия (RESTRICT, CASCADE или SET NULL) при обновлении или удалении значения родительского ключа. При вставках в родительскую таблицу не требуется никаких ссылочных действий, ибо еще не существует зависимых внешних ключей.

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

Некоторые из этих триггеров включают предложения, блокирующие строки (SELECT ... FOR UPDATE). Эта операция необходима для поддержания согласованности данных во время их обработки.

ТРИГГЕР ВНЕШНЕГО КЛЮЧА ДЛЯ ПОРОЖДЕННОЙ ТАБЛИЦЫ. Следующий триггер гарантирует, что предложение INSERT или UPDATE смогут создать новое значение внешнего ключа лишь тогда, когда существует соответствующее значение в родительском ключе. Ограничение MUTATING_TABLE, включенное в этот пример, позволяет использовать этот триггер в сочетании с триггерами UPDATE_SET_DEFAULT и UPDATE_CASCADE. Это исключение можно удалить, если данный триггер будет использоваться в одиночку.

CREATE TRIGGER emp_dept_check
BEFORE INSERT OR UPDATE OF deptno ON emp
FOR EACH ROW WHEN (new.deptno IS NOT NULL)

/* Перед появлением в таблице EMP нового значения DEPTNO,
возбудить этот триггер, чтобы проверить, что это значение
внешнего ключа (DEPTNO) существует в таблице DEPT. */

DECLARE
dummy INTEGER;  /* используется для извлечения из курсора */
invalid_department EXCEPTION;
valid_department EXCEPTION;
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (mutating_table, -4091);

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

CURSOR dummy_cursor (dn NUMBER) IS
SELECT deptno
FROM dept
WHERE deptno = dn
FOR UPDATE OF deptno;

BEGIN
OPEN dummy_cursor (:new.deptno);
FETCH dummy_cursor INTO dummy;

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

IF dummy_cursor%NOTFOUND THEN
RAISE invalid_department;
ELSE
RAISE valid_department;
END IF;

EXCEPTION
WHEN invalid_department THEN
raise_application_error(-20000,
'Invalid Department Number ' || TO_CHAR(:new.deptno));
WHEN valid_department THEN
CLOSE dummy_cursor;
WHEN mutating_table THEN
NULL;
END;

ТРИГГЕР UPDATE И DELETE RESTRICT ДЛЯ РОДИТЕЛЬСКОЙ ТАБЛИЦЫ.

Следующий триггер определяется по таблице DEPT, чтобы реализовать ссылочное действие UPDATE и DELETE RESTRICT по первичному ключу таблицы DEPT:

CREATE TRIGGER dept_restrict

AFTER DELETE OR UPDATE OF deptno ON dept
FOR EACH ROW

/* Перед удалением или изменением в таблице DEPT значения
первичного ключа (DEPTNO) проверить, что в таблице EMP
нет зависимых значений внешнего ключа; если они есть,
то возвратить ошибку. */

DECLARE
dummy INTEGER;  /* используется для извлечения из курсора */
employees_present EXCEPTION;
employees_not_present EXCEPTION;

/* Курсор, используемый для проверки зависимых внешних ключей */

CURSOR dummy_cursor (dn NUMBER) IS
SELECT deptno FROM emp WHERE deptno = dn;

BEGIN
OPEN dummy_cursor (:old.deptno);
FETCH dummy_cursor INTO dummy;

/* Если зависимый внешний ключ найден, инициировать
определенную пользователем ошибку. Если он не найден,
закрыть курсор, прежде чем продолжится предложение,
возбудившее этот триггер. */

IF dummy_cursor%FOUND THEN
RAISE employees_present;  /* существуют зависимые строки */
ELSE
RAISE employees_not_present;  /* нет зависимых строк */
END IF;

EXCEPTION
WHEN employees_present THEN
raise_application_error(-20001,
'Employees Present in Department '||TO_CHAR(:new.deptno));
CLOSE dummy_cursor;
WHEN employees_not_present THEN
CLOSE dummy_cursor;
END;

Замечания: Этот триггер не будет работать с самоссылочными таблицами (т.е. таблицами, содержащими как первичный/уникальный ключ, так и внешний ключ).

Кроме того, этот триггер не позволяет триггерам зацикливаться (например, A возбуждает B, который возбуждает A).

ТРИГГЕРЫ UPDATE И DELETE SET NULL ДЛЯ РОДИТЕЛЬСКОЙ ТАБЛИЦЫ. Следующий триггер, определяемый по таблице DEPT, реализует ссылочное действие UPDATE и DELETE SET NULL по первичному ключу таблицы DEPT:

CREATE TRIGGER dept_set_null


AFTER DELETE OR UPDATE OF deptno ON dept
FOR EACH ROW

/* Перед удалением или изменением в таблице DEPT значения
первичного ключа (DEPTNO) сбросить в NULL все зависимые
значения внешнего ключа в таблице EMP. */

BEGIN
IF UPDATING AND :OLD.deptno != :NEW.deptno THEN
UPDATE emp SET emp.deptno = NULL
WHERE emp.depnto = :old.deptno;
END IF;
END;

ТРИГГЕР DELETE CASCADE ДЛЯ РОДИТЕЛЬСКОЙ ТАБЛИЦЫ. Следующий триггер, определяемый по таблице DEPT, реализует ссылочное действие DELETE CASCADE по первичному ключу таблицы DEPT:

CREATE TRIGGER dept_del_cascade


AFTER DELETE ON dept
FOR EACH ROW

/* Перед удалением строки из таблицы DEPT удалить из таблицы
EMP все строки, имеющие такое же значение DEPTNO. */

BEGIN
DELETE FROM emp
WHERE emp.depnto = :old.deptno;
END;

Замечание: Обычно код для DELETE CASCADE объединяют вместе с кодом для UPDATE SET NULL или UPDATE SET DEFAULT, чтобы учесть как обновления, так и удаления в одном триггере.

ТРИГГЕР UPDATE CASCADE ДЛЯ РОДИТЕЛЬСКОЙ ТАБЛИЦЫ. Следующий триггер гарантирует, что при изменении номера отдела в таблице DEPT это изменение будет распространено на все зависимые внешние ключи в таблице EMP:

/* Создать последовательность, номер которой будет применяться
как флажок, указывающий, что данный столбец подвергается
обновлению. */

create sequence update_sequence increment by 1 maxvalue 5000
cycle;

CREATE PACKAGE integritypackage AS
updateseq NUMBER;
END integritypackage;

CREATE OR REPLACE PACKAGE BODY integritypackage AS
END integritypackage;

ALTER TABLE emp ADD update_id NUMBER;  /* добавить флажок */

CREATE TRIGGER dept_cascade1
BEFORE UPDATE OF deptno ON dept
DECLARE
dummy NUMBER;

/* Перед обновлением таблицы DEPT (это триггер предложения),
сгенерировать новый номер последовательности и назначить
его общей переменной UPDATESEQ из пакета INTEGRITYPACKAGE. */

BEGIN
SELECT update_sequence.NEXTVAL
INTO dummy
FROM dual;
integritypackage.updateseq := dummy;
END;

CREATE TRIGGER dept_cascade2
AFTER DELETE OR UPDATE OF deptno ON dept
FOR EACH ROW

/* Для каждого обновляемого значения DEPTNO в таблице DEPT
выполнить каскадное обновление зависимых внешних ключей
в таблице EMP. Выполняя каскадное обновление, проверять,
чтобы порожденная строка уже не была обновлена этим же
триггером. */

BEGIN
IF UPDATING THEN
UPDATE emp
SET deptno := :new.deptno,
update_id = integritypackage.updateseq -- из 1-го триггера
WHERE emp.deptno = :old.deptno
AND update_id IS NULL;

/* этот флажок пуст только в случае, если он не был
обновлен 3-м триггером для того же самого предложения
триггера */

END IF;

IF DELETING THEN

/* Перед удалением строки из таблицы DEPT, удалить из таблицы
EMP все строки, имеющие такое же значение DEPTNO, что и
в удаляемой строке таблицы DEPT */

DELETE FROM emp
WHERE emp.deptno = :old.deptno;
END IF;
END;

CREATE TRIGGER dept_cascade3
AFTER UPDATE OF deptno ON dept
BEGIN
UPDATE emp
SET update_id = NULL
WHERE update_id = integritypackage.updateseq;
END

Замечание: Так как этот триггер обновляет таблицу EMP, триггер EMP_DEPT_CHECK, если он включен, также возбуждается. Возникает ошибка (поскольку таблица EMP мутирующая), которую триггер EMP_DEPT_CHECK перехватывает. Вы должны тщательно отладить все триггеры, для которых требуется перехват ошибок, чтобы убедиться, что они всегда работают правильно в вашем окружении.

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

CREATE TRIGGER salary_check
BEFORE INSERT OR UPDATE OF sal, job_classification ON emp
FOR EACH ROW
DECLARE
minsal                NUMBER;
maxsal                NUMBER;
salary_out_of_range   EXCEPTION;

BEGIN

/* Извлечь в MINSAL и MAXSAL минимальное и максимальное
жалованья из справочной таблицы SALGRADE по заданной
новой должности сотрудника. */

SELECT minsal, maxsal INTO minsal, maxsal FROM salgrade
WHERE job_classification = :new.job_classification;

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

IF (:new.sal < minsal OR :new.sal > maxsal) THEN
RAISE salary_out_of_range;
END IF;

EXCEPTION
WHEN salary_out_of_range THEN
raise_application_error (-20300,
'Salary ' || TO_CHAR(:new.sal) ||
' out of range for job classification ' ||
:new.job_classification ||
' for employee ' || :new.name);
WHEN NO_DATA_FOUND THEN
raise_application_error (-20322,
'Invalid Job Classification ' || :new.job_classification);
END;

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

Лучше всего для комплексной проверки полномочий использовать триггер предложения BEFORE. Это дает следующие преимущества:

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

Пример

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

CREATE TRIGGER emp_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON emp
DECLARE
dummy INTEGER;
not_on_weekends EXCEPTION;
not_on_holidays EXCEPTION;
non_working_hours EXCEPTION;
BEGIN
/* проверить на выходные */
IF (TO_CHAR(sysdate, 'DY') = 'SAT' OR
(TO_CHAR(sysdate, 'DY') = 'SUN' OR THEN
RAISE not_on_weekends;
END IF;
/* проверить на праздники */
SELECT COUNT(*) INTO dummy FROM company_holidays
WHERE TRUNC(day) = TRUNC(sysdate);
/* TRUNC отсекает компоненту времени

из даты */
IF dummy > 0 THEN
RAISE not_on_holidays;
END IF;
/* Проверить на рабочие часы (8am .. 6pm) */
IF (TO_CHAR(sysdate, 'HH24') < 8 OR
(TO_CHAR(sysdate, 'HH24') > 18 THEN
RAISE non_working_hours;
END IF;
EXCEPTION
WHEN not_on_weekends THEN
raise_application_error (-20324,
'May not change employee table during the weekend');
WHEN not_on_holidays THEN
raise_application_error (-20325,
'May not change employee table during a holiday');
WHEN non_working_hours THEN
raise_application_error (-20326,
'May not change employee table during non-working hours');
END;

Триггеры весьма полезны, когда вы хотите организовать прозрачное выполнение обновлений базы данных, связанных с определенными событиями.

Например, триггер REORDER на странице 8-14 показывает пример триггера, который осуществляет повторный заказ товара, когда имеют место определенные условия (а именно, количество товара в наличии, PART_ON_HANDS, меньше, чем предписанное значение, REORDER_POINT).

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

• Зависимые значения должны быть вычислены перед тем, как произойдет вставка или обновление, так, чтобы предложение триггера могло использовать вычисленные значения.
• Триггер должен возбуждаться для каждой строки, которую затрагивает возбуждающее триггер предложение INSERT или UPDATE.

Пример

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

BEFORE INSERT OR UPDATE OF ename ON emp

/* Перед появлением нового значения поля ENAME, вычислить
значения полей UP
PERNAME и SOUNDEXNAME. Следует запретить
пользователям обновлять эти поля непосредственно. */

FOR EACH ROW
BEGIN
:new.uppername := UPPER(:new.ename);
:new.soundexname := SOUNDEX(:new.ename);
END;

Триггеры можно использовать для того, чтобы обеспечить синхронное сопровождение дубликатов таблицы на разных узлах распределенной базы данных. За примером такого типа триггера обратитесь к главе 12, "Управление снимками".

предыдущая часть | содержание | следующая часть

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Oracle
Отправить ссылку на страницу по e-mail