6.10. Триггеры

Часто разработчикам приходится реализовывать сложные алгоритмы поддержки целостности данных. Использование ограничений целостности Primary Key, Foreign Key и других предоставляют разработчикам достаточно эффективные механизмы обеспечения целостности данных. Однако их бывает недостаточно. Например, с помощью упомянутых механизмов нельзя разрешить изменение данных в том случае, если в одном из столбцов находится определенное значение.

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

Триггеры (triggers) в SQL Server 2000 представляют собой набор команд Transact-SQL, выполняемых автоматически при осуществлении тех или иных модификаций данных в таблице. Физически триггеры являются ни чем иным, как хранимыми процедурами специального типа. Каждый триггер связан с конкретной таблицей и запускается сервером автоматически каждый раз, когда пользователи пытаются произвести вставку, изменение или удаление данных. Триггер получает всю информацию о выполняемых пользователем изменениях в таблице. Разработчик реализовывает в триггере необходимые проверки и изменения данных в других таблицах базы данных.

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

Триггеры различаются по типу команд, на которые они реагируют:

• INSERT TRIGGER – запускаются при попытке вставить данные с помощью команды INSERT;

• UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE;

• DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE.

Триггеры не запускаются при выполнении команд обработки текстовых блоков, таких как WRITETEXT, UPDATETEXT, READTEXT.

Параметры FOR, AFTER и INSTEAD OF, указываемые при создании триггера, определяют его поведение следующим образом:

• FOR – запуск триггера при выполнении заданной в этом списке команды;

• AFTER – запуск триггера после успешного выполнения команд списка, триггеры данного класса могут быть созданы только для таблиц, и не могут быть созданы для представлений;

• INSTEAD OF – триггер вызывается вместо выполнения команд списка, могут быть определены как для таблиц, так и для представлений.

Можно определить несколько AFTER – триггеров для каждой операции INSERT, UPDATE и DELETE. По умолчанию все триггеры являются AFTER – триггерами. Триггеры нельзя создавать для временных или системных таблиц. Команда создания триггера должна быть первой в пакете и применяться только к одной таблице. Ее формат следующий:

CREATE TRIGGER Имя триггера

ON {Имя таблицы\Имя представления}

[WITH ENCRYPTION] -- шифрование кода триггера;

{ {{FOR\AFTER\INSTEAD OF}

{[DELETE] [,] [INSERT] [,] [UPDATE]}

[WITE APPEND] -- только для версий 6.5 и ниже;

[NOT FOR REPLICATION] -- не для репликации;

AS sql_statement […n] -- тело триггера;

}

|

{{FOR\AFTER\INSTEAD OF}

{[INSERT] [,] [UPDATE]}

[WITE APPEND] -- только для версий 6.5 и ниже;

[NOT FOR REPLICATION] -- не длярепликации;

AS {IF UPDATE (column) -- при изменении столбца;

[{AND\OR}UPDATE (column) […n]]}-- тоже;

IF (COLUMNS_UPDATED() {bitwise_operator}

Update_bitmask)

{comparison_operator}column_bitmask […n]

}

sql_statement […n] -- тело триггера.

}

}

Вторая альтернатива команды {IF UPDATE…} используется для детального анализа изменений содержимого колонок с помощью специальных функций, битовых масок, операторов побитовой обработки, оператор сравнения и логических операторов.

Команда AFTER TRIGGER позволяет изменить параметры и тело триггера. С помощью команды DROP TRIGGER можно удалить любой триггер базы данных. Переименовать триггер можно системной хранимой процедурой sp_rename, а получить информацию о триггере можно при помощи системных хранимых процедур sp_helptext и sp_helptrigger.

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