ТЕМА 4.2. ХРАНИМЫЕ ПРОЦЕДУРЫ И ТРИГГЕРЫ

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

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

Допустим, что в базе данных есть таблицы, связанные через поле Surname. Например, это могут быть таблица клиентов предприятия и их заказов. Ра­зумно определить триггер, который при каждой попытке удалить запись клиента проверит наличие у него заказов и позволит удалить эту запись только при их отсутствии. Конечно, подобную задачу можно решить при помощи средств декларативной ссылочной целостности. Однако при помо­щи триггеров можно создавать значительно более сложные рабочие правила. Можно создать триггер, который при каждом добавлении записи в таблицу заказов анализирует предыдущие заказы этого же клиента и определяет при­емлемый срок оплаты этого заказа.

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

Триггеры могут иметь до 32 уровней вложенности. Вложенные триггеры ра­ботают следующим образом: пусть при создании записи о новом заказе триггер добавляет информацию в таблицу неоплаченных счетов. При этом выполняется другой триггер, который проверяет, имеет ли клиент просро­ченные неоплаченные счета и, если они есть, триггер выводит сообщение об этом. В этом примере один триггер обновляет таблицу, вызывая при этом выполнение другого триггера.

По умолчанию все триггеры (INSERT, DELETE, UPDATE) срабатывают сразу по­сле выполнения операции изменения данных. Эти триггеры относятся к ти­пу AFTER (после). Начиная с SQL Server 2000 появилась еще одна группа триггеров - INSTEAD OF (вместо), которые выполняются вместо оператора, предполагающего изменение данных.

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

Триггер является частью транзакции, следовательно, если триггер терпит неудачу, отменяется вся транзакция. И наоборот, если какая-то часть тран­закции не удалась, то и триггер будет отменен.

В своей работе триггеры используют таблицы Inserted и Deleted. Это логиче­ские таблицы, они постоянно находятся в оперативной памяти и имеют туже структуру, что и таблица, для которой создан триггер. Каждая добавлен­ная к защищаемой триггером таблице строка добавляется и в таблицу Inserted. Обновление производится почти так же, как и удаление с после­дующей вставкой. Когда строка обновляется, старая строка записывается в таблицу Deleted, затем обновленная строка записывается в базовую табли­цу и в таблицу Inserted.

Создание триггера

Триггер создается оператором CREATE TRIGGER. Рассмотрим его синтаксис:

CREATE TRIGGER [владелец.] имя_триггера

ON [владелец.] имя_таблицы │имя_представления

FOR (AFTER│ INSTEAD OF} (INSERT│UPDATE I DELETE}

[WITH ENCRYPTION]

AS оператор_SQL

Таблица может иметь произвольное количество триггеров любых типов (INSERT, UPDATE или DELETE). ПО умолчанию триггер выполняется после из­менения данных, однако, если указать параметр INSEAD OF, то создается триггер, выполняющийся вместо изменения данных.

Каждая операция (INSERT, UPDATE и DELETE) может вызывать выполнение произвольного количества триггеров. С единственным ограничением ­имена триггеров, вызываемых одной операцией, должны быть уникальными. Изменить триггер можно, удалив его и создав заново в другом виде или при помощи оператора ALTER TRIGGER. При удалении таблицы, имеющей триг­геры, все они также удаляются.

При создании триггеров необходимо придерживаться следующих правил:

• Триггеры создаются для поддержания целостности данных, ссылочной целостности и рабочих правил.
• Нельзя создавать триггеры для временных таблиц. Однако триггеры могут к таким таблицам обращаться так же, как и к представлениям.
• Триггер не может возвращать результирующих наборов данных. Это зна­чит, что к использованию оператора SELECT при его создании нужно подходить крайне осторожно. Обычно в этих случаях используется оператор SELECT с директивой IF EXISTS.
• С помощью опции WITH ENCRYPTION исходный код триггера, хранящийся в таблице syscomments, можно зашифровать.
• Операторы WRITETEXT не инициализируют триггеры. Они используются для изменения данных типа Text или Image, а эти изменения не заносят­ся в журнал транзакций.
• В триггерах нельзя использовать следующие операторы: все операторы CREATE, все операторы DROP, ALTER TAВLE, ALTER DATAВASE, TRUNCATE TABLE, GRANT и REVOKE, RECONFIGURE, LOAD DATAВASE или TRANSACTION, UPDATE STATISTICS, SELECT INTO и все операторы DISK.
• Операторы отмены транзакций, входящие в состав триггера, могут стать причиной непредсказуемого поведения операторов вызывающей программы.

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

Отдельное внимание стоит уделить триггерам типа INSTEAD OF. Если триггер создается с этой опцией, то код триггера выполняется не после заданной пользователем (или удаленной программой) команды, а вместо нее. Напри­мер, разумно использовать триггеры INSTEAD OF для сообщений о невоз­можности удаления какого-либо объекта. Разумеется, эту функцию можно реализовать и с помощью триггера AFTER, однако в этом случае придется отменять уже проделанную операцию, что неприемлемо для высоко загру­женных систем, где ведется строгий контроль производительности.