Общая методология
		Новости и события
		Опыт пользователей
		Oracle Applications
		Высокие технологии
		Постоянная экспозиция
		Мастерская разработчика
		Кабинет администратора
		Полезные ссылки
		Архив

Издатель:
Algorithm Online

При поддержке
ORACLE в России


Техническая
поддержка

официальный
дистрибутор
ORACLE

МАСТЕРСКАЯ РАЗРАБОТЧИКА

Как избежать ошибок в триггерах

1 июля 2000 г.

Avoiding Trigger Errors, By Egil P. Andersen

Эгл Андерсен

Oracle Magazine, no.2, 1999.
http://www.oracle.com/oramag/oracle/99-Mar/29dba.html

Излагаемая схема рекомендуется для упорядочения триггеров и предотвращения ошибок, вызванных изменениями (mutating) и ограничениями (constraining) таблиц.

Триггеры в базе данных Oracle представляют собой программные модули, выполняющиеся автоматически, когда происходят определенные события в таблице базы данных. Триггеры полезны во многих отношениях, например, их можно использовать для определения сложных ограничений целостности, которые не могут быть обеспечены декларативными ограничениями целостности, а также для автоматизации таких задач, как обновление или удаление одних записей таблицы при изменении или внесении в нее других записей. Мы широко используем триггеры в нашей базе данных Центра информационных технологий университета города Осло, в разработанной нами административной системе, называющейся Felles Studentsystem.

Система сейчас обслуживает порядка пятисот зарегистрированных пользователей в Осло. Клиентская часть системы написана на средстве PowerBuilder фирмы Sybase, в качестве сервера базы данных используется СУБД Oracle. Исходный текст системы включает порядка ста пятидесяти тысяч строк на языках SQL и PL/SQL, из которых чуть более двадцати пяти тысяч строк представляют собой коды триггеров. Мы используем триггеры для решения многих задач от оценки кандидатов до обеспечения системы приоритетов. Наши триггеры гарантируют, что как обработка данных, так и все необходимые проверки происходят на сервере, вне зависимости от того, сколько приложений вовлечено в процесс.

Можно создать триггеры двенадцати разных типов, выбирая тип триггера из столбцов Таблицы 1. (В Oracle8 введены триггеры типа “instead of”, которые в этой статье не рассматриваются.) Например, можно создать триггер "после удаления записи" (delete after row), который выполняется для каждой удаляемой записи после завершения удаления. Триггер "перед командой удаления" (delete before statement) выполняется ровно один раз, вне зависимости от количества удаляемых записей (их может вообще не быть), перед выполнением команды удаления.

Таблица 1: Возможные триггерные комбинации

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

• "Перед началом выполнение команды" (Before-statement trigger)
• "Перед обработкой записи" (Before-row trigger)
• "После обработки записи" (After-row trigger)
• "После окончания выполнения команды" (After-statement trigger)

В триггере табличного уровня ("Перед началом выполнение команды" или "После окончания выполнения команды") нет возможности получить информацию об обрабатываемых записях. Например, в триггере, выполняющемся после команды внесения записи, нет возможности понять, какие записи были внесены командой, вызвавшей срабатывание триггера. Но при выполнении триггера уровня записи ("Перед обработкой записи" или "После обработки записи"), можно явно получить информацию о значениях полей обрабатываемой записи. Например, система выполняет триггер "после внесения записи" (insert-after-row) один раз для каждой вносимой записи. A в триггере "перед внесением или обновлением записи" (insert- or update-before-row) можно даже изменять значения полей каждой обрабатываемой записи.

Триггеры полезны лишь тогда, когда они правильно реализованы, поэтому очень важно понимать, как их использовать, чтобы избежать ошибок при выполнении, таких как ORA-4091 и ORA-4094. Эти ошибки, вызванные ограничениями (Constraining) и изменениями (Mutating) таблиц, обычно вынуждают полностью переписать соответствующий триггер.

• Таблица считается измененной (Mutating) при выполнении триггера, если она модифицируется командой, инициировавшей срабатывание триггера.
• Таблица считается ограниченной (Constraining) при выполнении триггера, если она читается командой, инициировавшей срабатывание триггера для обеспечения декларативных ограничений целостности.

Таблицы являются ограниченными и измененными триггерами табличного уровня, только если такие триггеры выполняются в результате каскадного удаления (возможность определения декларативного ограничения ссылочной целостности в СУБД Oracle с помощью опции Delete Cascade) или их выполнение было инициировано действиями триггера уровня записи. Чтобы предотвратить эти ошибки надо следовать двум простым правилам:

• Правило А. Измененная таблица не может читаться (с помощью оператора select) или модифицироваться (с помощью insert, update или delete) во время выполнения соответствующего триггера
• Правило В. Ограниченная таблица не может модифицироваться во время выполнения соответствующего триггера

Хотя эти правила очень просты, они накладывают серьезные ограничения на создание триггеров. Например, рассмотрим две таблицы, Tab и Ref. Tab содержит два столбца: pk_tab первичный ключ и col_code. Ref также содержит два столбца: pk_ref первичный ключ и fk_tab внешний ключ на таблицу Tab. Внешний ключ fk_tab определяется декларативным ограничением целостности без опции каскадного удаления. Рекурсивные вызовы триггеров нуждаются в особой обработке (см. раздел “Использование рекурсивных вызовов триггеров”).

Эти четыре ограничения накладываются на таблицы Tab и Ref:

• Ограничение 1. Не более пяти записей в таблице Tab могут иметь одно и тоже значение поля col_code.
• Ограничение 2. При внесении новой записи в таблицу Tab система создает ссылающуюся на нее запись в таблице Ref.
• Ограничение 3. При изменении (обновлении) внешнего ключа таблицы Ref, должна произойти ошибка, если исчезнет последняя запись из таблицы Ref, ссылающаяся на определенную запись таблицы Tab.
• Ограничение 4. При переопределении внешнего ключа записи таблицы Ref на несуществующую запись таблицы Tab, система должна создать новую запись в таблице Tab с соответствующим значением первичного ключа. (Только в случае, если не нарушается ограничение 2)

Прямолинейное решение для определения вышеперечисленных ограничений было бы таким:

• Для обеспечения ограничения 1 создадим триггер, выполняющийся при внесении и изменении записей таблицы Tab, который будет выполнять запрос для проверки этого ограничения.
• Для обеспечения ограничения 2 создадим триггер, выполняющийся при внесении записи в таблицу Tab, который будет создавать новую запись в таблице Ref.
• Для обеспечения ограничения 3 создадим триггер, выполняющийся при обновлении записей таблицы Ref, который будет выполнять запрос для проверки этого ограничения.
• Для обеспечения ограничения 4 создадим триггер, выполняющийся при обновлении записей таблицы Ref, который будет создавать новую запись в таблице Tab, в том случае, если запись, на которую определена ссылка, не существует.

Однако, триггеры, созданные для обеспечения ограничений 1 и 2 приведут к появлению ошибки, по причине нарушения правила A, так как таблица Tab будет считаться измененной. Триггеры, созданные для обеспечения ограничения 3 приведет к появлению такой же ошибки для таблицы Ref. Триггер для обеспечения ограничения 4 также приведет к появлению ошибки из-за нарушения правила B, так как таблица Tab будет ограниченной.

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

В Таблице 2 (Триггерные ошибки, как результат измененности или ограниченности таблиц при задействовании декларативных ограничений целостности) перечислены ошибки, которые могут быть вызваны изменяемой или ограничиваемой таблицей из-за наличия декларативных ограничений ссылочной целостности (внешних ключей) между таблицами Tab и Ref, если перечисленные команды будут выполняться в соответствующих триггерах уровня записи. (Заметим, что ошибка ORA-4094 отсутствует в Oracle8i. В этой версии Oracle вы можете изменять таблицу, которая читается для поддержания ограничений целостности, но все еще нельзя изменять таблицу, являющуюся измененной.) Например:

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

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

Тип Триггера

Триггерное Предложение

	Insert в таблицу Ref	Update таблицы Ref	Delete из таблицы Ref
Insert row trigger для Tab	Tab изменена (Mutating)	Tab изменена (Mutating)	OK
Update row trigger для Tab	Tab изменена (Mutating)	Tab изменена (Mutating)	Ref ограничена (Constraining)
Delete row trigger для Tab	Tab изменена (Mutating)	Tab изменена (Mutating)	Ref ограничена (constraining)

Тип Триггера

Триггерное Предложение

	Insert в таблицу Tab	Update таблицы Tab	Delete из таблицы Tab
Insert row trigger для Ref	Tab ограничена (Constraining)	Ref изменена (Mutating)	Ref изменена (Mutating)
Update row trigger для Ref	Tab ограничена (Constraining)	Ref изменена (Mutating)	Ref изменена (Mutating)

Профилактика

Для нашей системы, я разработал решение, которое предоставляет общую структуру триггеров, в которой триггеры уровня записи используются для сохранения информации о записях, изменяемых командой, вызвавшей срабатывание триггера. Триггеры табличного уровня используются собственно для поддержания ограничений целостности, касающихся измененяемых записей, как это показано в Листинге1. Общая структура организации триггеров для предотвращения ошибок, представленная на Листинге1, состоит из единственного пакета на PL/SQL и набора триггеров всевозможных типов для каждой таблицы.

Пакет PK_Triggnnn запоминает каждую запись, измененную командой, вызвавшей срабатывание соответствующего триггера, и обеспечивает целостность измененных данных. Тело пакета PK_Triggnnn содержит две таблицы PL/SQL, называющиеся newList и oldList. Каждая из этих таблиц содержит записи с атрибутами соответствующими каждому столбцу таблицы базы данных <table>:

• newList содержит значения полей всех записей, внесенных или обновленных командой, вызвавшей срабатывание соответствующего триггера.
• oldList содержит значения полей всех записей, удаленных или обновленных командой, вызвавшей срабатывание соответствующего триггера. В случае обновления, в таблице сохраняются значения старые значения полей, существовавшие до обновления.
• newCount# и oldCount# представляют собой счетчики, указывающие на номер последней записи в newList и oldList соответственно. Когда таблицы PL/SQL пусты, значения счетчиков 0.

Кроме того, Листинг 1 содержит интерфейсные процедуры для обеспечения управления и записи информации в таблицы пакета. Процедура P_Reset очищает newList и oldList, присваивая им значение emptyList и устанавливая значения счетчиков в 0. Процедура P_PutRowList записывает информацию о записи, затронутой командой, вызвавшей срабатывание триггера. У неё есть два входных параметра inRec и isNew: inRec представляет собой запись с атрибутами соответствующими столбцам таблицы базы данных <table>. Если логический параметр isNew имеет значение ИСТИНА, тогда запись - значение параметра inRec записывается в таблицу newList; в противном случае - в oldList. Нет необходимости сохранять значение для каждого столбца таблицы <table>, на которой определен триггер. Значения каких именно столбцов нужно сохранить, зависит от ситуации, но, как минимум, включаются значения полей первичного или уникального ключа, для однозначной идентификации обрабатываемой записи.

В случае если таблица <table> имеет много атрибутов, из которых значения лишь нескольких должны быть сохранены, вам нужно лишь заменить эту троку в теле пакета PK_Triggnnn:

Type RowListType Is Table Of <table>%RowType Index By Binary_Integer;

на эти строки:

Type SaveRecord Is Record ( < Тип данных Column_1>, .....<другие столбцы>....., < Тип данных Column_n>);

Type RowListType Is Table Of SaveRecord Index By Binary_Integer;

(В этом примере ... имена столбцов, значения которых должны быть сохранены.)

Интерфейсная функция F_EmptyRowList возвращает значение ИСТИНА, если newList или oldList пусты; в противном случае, функция возвращает значение ЛОЖЬ. Если параметр isNew имеет значение ИСТИНА, функция проверяет таблицу newList; в противном случае таблицу oldList.

Интерфейсные процедуры P_Handle_Insert, P_Handle_ Update и P_Handle_Delete содержат программные код, предназначенный для обеспечения ограничений целостности для записей, затронутых командой, инициировавшей срабатывание триггера. Эти процедуры обрабатывают по одной записи за раз. P_Handle_Insert просматривает таблицу newList, P_Handle_Delete просматривает таблицу oldList, и P_Handle_Update просматривает и oldList, и NewList одновременно.

Вы можете использовать интерфейсную переменную пакета triggersEnabled для включения и отключения всех триггеров, определенных на таблице базы данных <table>. Обычно, требуется добавить несколько похожих переменных-переключателей для включения и отключения различных частей кода триггеров. В противоположность команде SQL Alter Table, отключающей и включающей триггеры, отключение с помощью переменной действует лишь в рамках соответствующей сессии. Это означает, что поведение может варьироваться для различных сессий: отключение или включение в одной, конкретной сессии не затронет другие сессии.

Листинг 2 и Листинг 3 содержат триггеры для таблиц Tab и Ref соответственно, используя структуру из Листинга 1 для обеспечения четырех ограничений целостности, перечисленных выше.

Вы можете использовать интерфейсные переменные пакета triggersEnabled и insertRefOnInsert (в Листинге2) для включения или отключения триггеров для текущей сессии. Когда вы их используете, очень важно понять, что необходимо делать в ситуации, когда происходит ошибка. Предположим, что ошибка происходит при изменении таблицы Ref в процедуре PK_Trigg002.P_UpdateFK после того, как переменной PK_Trigg002.triggersEnabled присвоено значение ЛОЖЬ, означающее отключение триггеров на таблице Ref. В этом случае выполнение процедуры будет прервано, не дойдя до команды, присваивающего значение ИСТИНА переменной пакета K_Trigg002.triggersEnabled, следующей за командой обновления. Без обработчика исключительных ситуаций, триггеры на таблице Ref останутся отключенными после отката транзакции. Обработчик исключений установит глобальные переменные в корректные значения после завершения транзакции (вне зависимости от того, будет она подтверждена или нет) и инициирует ошибку.

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

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

• Вы можете определить декларативное ограничение ссылочной целостности с опцией On Delete Cascade и реализовать триггеры так, чтобы избежать этих ошибок.
• Вы можете определить обычное (не каскадное) ограничение целостности внешнего ключа и реализовать каскадное удаление с помощью триггеров.

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

Egil P. Andersen (egil.andersen@nr.no) доктор технических наук Университета города Осло, ученый-исследователь Норвежского вычислительного центра, расположенного в городе Осло. Он исполнял обязанности главного инженера Университета города Осло с 1995 по 1997 годы.

Листинг 1

Листинг 2

Листинг 3

	Материал номера: Новый тест для специалистов по Oracle
	Колонка главного редактора: Oracle открывает третье тысячелетие.  Письмо в редакцию
	Человек месяца: Беседа с руководителями ИВЦ АИС и WEB-центра “Омега” Oracle: от МВД до РПЦ