Раздел II. Создание триггеров в языке transact-sql.

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

В SQL Server 2000/2005 существует два вида триггеров:

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

· INSTEAD OF-триггеры, тело которых выполняется вместо операций вставки, обновления и удаления строк, вызвавших запуск триггера этого вида. С каждой таблицей может быть связано не более трех AFTER-триггеров (по одному для каждой из команд INSERT, UPDATE, DELETE). Триггеры этого вида могут создаваться не только для таблиц, но и для представлений.

Синтаксис команды создания триггера (см. [1], стр. 1242):

 

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ FOR [ { AFTER | INSTEAD OF }]

{ [ DELETE ] [, ] [ INSERT ] [, ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[... n ]
| IF ( COLUMNS_UPDATED () { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [... n ]
} ]
sql_statement [... n ]
}
}

 

В этой команде, в частности, присутствуют функции UPDATE(column) и COLUMNS_UPDATED(), используемые для определения того, какой столбец или группу столбцов пользователь пытается изменить (см. [1], стр. 1244-1246).
Кроме того, всегда можно получить полную информацию о изменениях, которые пытается произвести пользователь. Эту информацию дают таблицы inserted и deleted, которые автоматически создаются сервером при запуске триггера. Содержимое этих таблиц зависит от команды, вызвавшей запуск триггера:

· Команда INSERT. В таблице inserted будут содержаться все строки, которые пользователь пытается вставить в таблицу. Таблица deleted будет пуста.

· Команда DELETE. В таблице deleted будут содержаться все строки, которые пользователь пытается удалить. Таблица inserted будет пуста.

· Команда UPDATE. В таблице deleted будут содержаться все строки, которые пользователь пытается изменить. В таблице inserted указываются строки, которые будут внесены в таблицу вместо соответствующих строк таблицы deleted.

Для внесения изменений в текст существующего триггера используется та же команда, что и для его создания, с тем лишь отличием, что вместо зарезервированного слова CREATE используется слово ALTER (см. [1], стр. 1246).

Для удаления триггера используется команда, имеющая следующий синтаксис (см. [1], стр. 1247):

 

DROP TRIGGER { trigger } [,...n ]

 

В кодах триггеров часто используется команда ROLLBACK TRAN (отмена или откат транзакции). Кроме нее в программах на языке Transact-SQL используются также команды BEGIN TRAN (старт транзакции), COMMIT TRAN (подтверждение транзакции) и SAVE TRAN (создание точки сохранения транзакции). Подробнее см. в [1], стр. 1247. Например, отладку какой-нибудь команды или фрагмента программы, вносящих изменения в данные, можно начать со старта транзакции, а в самом конце выполнить команду ROLLBACK TRAN, восстановив тем самым первоначальные значения измененных данных:

É

SELECT * FROM Валюта -- просмотр исходных данных

BEGIN TRAN -- старт транзакции

UPDATE Валюта -- обновление данных

SET КурсВалюты = КурсВалюты * 2

SELECT * FROM Валюта -- просмотр измененных данных

ROLLBACK TRAN -- откат транзакции

 

SELECT * FROM Валюта -- снова просмотр исходных данных

GO

Ç

 

Рассмотрим два примера по созданию триггеров.

Пример 1. Запретим с помощью триггера возможность модификации данных в столбце ДатаЗаказа таблицы Заказ.

É

CREATE TRIGGER tr_Заказ_ДатаЗаказа

ON Заказ

FOR UPDATE AS

IF UPDATE(ДатаЗаказа)

BEGIN

PRINT 'Обновление столбца "ДатаЗаказа" запрещено'

ROLLBACK TRAN -- откат транзакции

END

GO

Ç

Примечание. Команда PRINT в отличие от команды SELECT выводит сообщения не на панель Results, а на панель Messages окна Query утилиты SQL Server Management Studio.

Теперь, не смотря на то, что вы являетесь владельцем базы данных, вы уже не можете редактировать значения столбца ДатаЗаказа в таблице Заказ:

É

SELECT * FROM Заказ -- команда 1

UPDATE Заказ -- команда 2

SET ДатаЗаказа = ДатаЗаказа + 10

GO -- команда 3

SELECT * FROM Заказ -- команда 4

Ç

Примечание. Все 4 команды, приведенные выше, выделите подсветкой в окне Query и выполните за один раз. Тогда будут показаны две таблицы Заказ (до и после корректировки).

Пример 2. Предварительно добавим в таблицу Заказ два новых столбца: Стоимость и СтоимостьНВ:

É