Глава 5. Триггеры DML

Триггеры DML — это хранимые процедуры особого типа, автоматически вступающие в силу, если происходит событие языка обработки данных DML, которое затрагивает таблицу или представление, определенное в триггере. События DML включают инструкции INSERT, UPDATE или DELETE. Триггеры DML могут использоваться для предписания бизнес-правил и правил целостности данных, выполнения запросов к другим таблицам и включения сложных инструкций Transact–SQL. Триггер и инструкция, при выполнении которой он срабатывает, считаются одной транзакцией, которую можно откатить назад внутри триггера.

Триггеры DML аналогичны ограничениям в том, что могут предписывать целостность сущностей или целостность домена. Вообще говоря, целостность сущностей должна всегда задаваться на самом нижнем уровне с помощью индексов, являющихся частью ограничений PRIMARY KEY и UNIQUE или создаваемых независимо от ограничений. Целостность домена должна быть предписана через ограничения CHECK, а ссылочная целостность — через ограничения FOREIGN KEY. Триггеры DML наиболее полезны в тех случаях, когда функции ограничений не удовлетворяют функциональным потребностям приложения.

В следующем списке приведено сравнение триггеров DML с ограничениями и указано, в чем триггеры DML имеют преимущества.

· Триггеры DML позволяют каскадно проводить изменения через связанные таблицы в базе данных; но эти изменения могут осуществляться более эффективно с использованием каскадных ограничений ссылочной целостности. Ограничения FOREIGN KEY могут проверить значения столбца только на предмет точного совпадения со значениями другого столбца.

· Для предотвращения случайных или неверных операций INSERT, UPDATE и DELETE и реализации других более сложных ограничений, чем те, которые определены при помощи ограничения CHECK. В отличие от ограничений CHECK, DML-триггеры могут ссылаться на столбцы других таблиц. Например, триггер может использовать инструкцию SELECT для сравнения вставленных или обновленных данных и выполнения других действий, например изменения данных или отображения пользовательского сообщения об ошибке.

· Чтобы оценить состояние таблицы до и после изменения данных и предпринять действия на основе этого различия.

· Несколько DML-триггеров одинакового типа (INSERT, UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.

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

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

· Если в таблице существуют ограничения, то их проверка осуществляется между выполнением триггеров INSTEAD OF и AFTER. В случае нарушения ограничений выполняется откат действий триггеров INSTEAD OF, а триггер AFTER не срабатывает.

Основной формат команды создания триггера

CREATE TRIGGER [имя_триггера]

ON имя_таблицы

{ FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,][ DELETE]}

[WITH ENCRYPTION]

AS SQL_операторы

используя предложение IF UPDATE

CREATE TRIGGER [имя_триггера]

ON имя_таблицы

{ FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] }

[WITH ENCRYPTION]

AS

IF UPDATE (имя_столбца) [{AND | OR} UPDATE (имя_столбца)...]

| IF (COLUMNS_UPDATES(){оператор_бит_обработки} бит_маска_изменения)

{оператор_бит_сравнения } бит_маска [...n]}

sql_оператор [...n]}

SQL_операторы

ON имя_таблицы – объявляется таблица или представление, от которых зависит триггер.

WITH ENCRYPTION имеет тот же смысл, что и для хранимых процедур.

{ FOR | AFTER | INSTEAD OF} – указывает, когда должен запускаться триггер. Ключевые слова FOR и AFTER являются синонимами.

IF UPDATE (имя_столбца) [{AND | OR} UPDATE (имя_

столбца)...] – позволяет выбрать конкретный столбец, запускающий триггер.

COLUMNS_UPDATES – возвращает битовый шаблон varbinary, который показывает, какие столбцы таблицы или представления добавлялись или изменялись.

В большинстве СУБД действуют следующие ограничения:

- Нельзя использовать в теле триггера операции создания объектов базы данных (новой базы данных, новой таблицы, новой хранимой процедуры, нового триггера, новых представлений).

- Нельзя использовать в триггере команду удаления объектов DROP для всех типов базовых объектов базы данных.

- Нельзя использовать в теле триггера команды изменения базовых объектов ALTER TABLE, ALTER DATABASE.

- Нельзя изменять права доступа к объектам базы данных, т.е. выполнять команду GRAND или REVOKE.

- Нельзя создать триггер для представления (VIEW).

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

- Внутри триггера не допускается выполнение восстановления резервной копии БД или журнала транзакций.

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

Триггер FOR (AFTER)

Триггеры FOR(AFTER) выполняются после выполнения действий инструкции INSERT, UPDATE или DELETE. Триггеры FOR(AFTER) никогда не выполняются, если происходит нарушение ограничения, поэтому эти триггеры нельзя использовать для какой-либо обработки, которая могла бы предотвратить нарушение ограничения. Если в результате работы триггера была обнаружена ошибка, то необходимо выполнить откат транзакции с помощью команды ROLLBACK TRANSACTION для отмены действия инструкции, если ошибок нет, то никаких дополнительных действий не нужно.