Триггер (trigger) – особый тип хранимой процедуры, автоматически выполняемый при определенных событиях (обычно при изменении данных таблицы).
Типы триггеров:
• триггеры INSERT,
• триггеры DELETE,
• триггеры UPDATE,
• триггеры, создаваемые с учетом одновременного возникновения и совпадения событий.
Создание триггеров:
CREATE TRIGGER имя_триггера
ON имя_таблицы
[ WITH ENCRYPTION ]
FOR [ INSERT ] [, UPDATE ] [, DELETE ]
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS Операторы_SQL
WITH ENCRYPTION – скрытие кода (никто его не сможет вызвать, даже создатель триггера). В случае применения этого параметра нужно сохранить копию кода в другом месте.
WITH APPEND – указывает на то, что этот триггер должен быть введен в действие, даже если на этой таблице уже определен триггер такого же типа (для согласования с предыдущими версиями SQL Server, поскольку в SQL Server 2005 и так можно делать).
NOT FOR REPLICATION – если указана эта опция, то триггер не запускается каждый раз, когда модификация таблицы происходит в связи с выполнением задач, относящихся к репликации (т.е. не применяется к реплицируемой копии таблицы).
В разделе Операторы_SQL определение триггера среди других операторов SQL может включать оператор ROLLBACK TRAN [ SACTION ] (отменить транзакцию).
При работе с триггерами доступны две специальные таблицы – таблица вставок (inserted) и таблица удалений (deleted).
Таблица deleted содержит копии записей, затронутых операторами DELETE или UPDATE. При выполнении этих операторов записи удаляются из таблицы триггера и перемещаются в таблицу удалений. Т.е. обычно в таблице триггера и таблице удалений нет совпадающих записей.
В таблице inserted хранятся копии записей, затронутых операторами INSERT или UPDATE, при выполнении которых записи вставляются новые записи и в таблицу триггера и в таблицу вставок.
Просмотр информации о триггерах осуществляется с помощью хранимых процедур:
sp_helptext имя_триггера – выводит код триггера,
sp_depends имя_триггера – выводит список объектов триггера,
sp_helptrigger имя_таблицы – выводит список триггеров, определенных на указанной таблице.
Удаление триггера аналогично удалению других объектов (с помощью команды DROP)
Применение триггеров
Назначение триггера – предотвращение вставки, изменения или удаления записей, отвечающим некоторым условиям.
Например, если надо запретить вставлять в таблицу Заказчики записи о заказчиках из Москвы, можно применить следующий триггер:
CREATE TRIGGER ЗаказчикиМосквы
ON Заказчики
FOR INSERT, UPDATE
AS
IF EXISTS
( SELECT 'True' FROM Inserted JOIN Заказчики
ON Inserted.КодЗаказчика= Заказчики. КодЗаказчика
WHERE Заказчики.Город=’Москва’ )
ROLLBACK TRAN
То есть проверяется наличие в таблице Inserted записи, удовлетворяющей заданному условию. И в случае, когда такая надпись есть (т.е. ее вставляет пользователь) происходит отмена транзакции.
Задание 1. Составить триггер, запрещающий вставлять в таблицу Сотрудники записи о сотрудниках, старше 65 лет. Проверьте работу триггера.
Задание 2. Составить триггер, запрещающий удалять из таблицы Заказы записи о заказах, дата выполнения которых позднее сегодняшнего дня. Проверьте работу триггера.
Примечание: здесь надо проверять наличие записи только в таблице Deleted, т.е. не надо производить соединение таблиц (как это было для триггера в примере).
В триггерах возможно обращение к данным из связанной таблицы. Кроме того, можно предусмотреть вывод пользовательского сообщения об ошибке. Рассмотрим эти две возможности на примере.
Пример задачи: необходимо создать триггер, который не допускал бы указание заказа с сегодняшней датой выполнения в таблице Сотрудники-Заказы. Пусть триггер также предусматривает вывод соответствующего сообщения при попытке вставить запись о таком заказе.
CREATE TRIGGER ОтменаВставкиЗаказа
ON Сотрудники-Заказы
FOR INSERT, UPDATE
AS
IF EXISTS
( SELECT ‘True’ FROM Inserted JOIN Заказы
ON Inserted.НомерЗаказа=Заказы.НомерЗаказа
WHERE Convert (varchar(20), Заказы.ДатаВыполнения, 104) = Convert (varchar(20), GetDate(), 104)
BEGIN
RAISERROR (‘Заказы с сегодняшним днём исполнения не вставлять’,3,1)
ROLLBACK TRAN
END
Здесь конвертирование дат необходимо, чтобы сравнение их происходило в одинаковом формате, иначе даже одинаковые даты, но записанные в различных форматах, не будут восприниматься как равные.
Оператор RAISERROR предназначен для вывода сообщения об ошибке. Приведем лишь обязательные параметры этого оператора:
RAISERROR ( { номер_сообщения_об_ошибке | ‘строка’ | имя_переменной } ,
уровень_серьезности, номер_состояния )
номер_сообщения_об_ошибке - номер сообщения об ошибке, определенного пользователем, которое сохранено в представлении каталога sys.messages при помощи процедуры sp_addmessage. Номера ошибок для пользовательских сообщений об ошибках должны превышать 50000.
‘строка’ - это текст сообщения об ошибке, который не должен содержать более 2 047 символов.
имя_переменной - переменная любого допустимого типа данных для символов.
уровень_серьезности - определенный пользователем уровень серьезности, связанный с этим сообщением. Уровень серьезности от 0 до 18 может указать любой пользователь. Уровни серьезности от 19 до 25 могут быть указаны только пользователями с более широкими правами.
номер_состояния - произвольное целое число от 1 до 127. Если одна и та же пользовательская ошибка возникает в нескольких местах, то при помощи этого уникального номера состояния для каждого местоположения можно определить, в каком месте кода появилась ошибка.
Задание 3. Составить триггер, запрещающий вставлять в таблицу Заказы запись о заказах, сделанных заказчиками из Москвы. Предусмотрите вывод сообщения при попытке вставить такую запись. Проверьте работу триггера.
Задание 4. Составить триггер, запрещающий удалять из таблицы Сотрудники-Заказы записи, в которых участвуют сотрудники со стажем работы не более 2 лет. Предусмотрите вывод сообщения при попытке удалить такую запись. Проверьте работу триггера.
Задание 5. Просмотрите информацию о триггерах, заданных на таблицах Заказы и Сотрудники.
Посетил аукцион, прикупил несколько отличных вещей очень дешево. Рекомендую.
Вкуснейшие круассаны - пальчики оближешь.
Профессиональные электростанции - энергии хватит, гарантированно.
