Использование триггеров
Ограничения при создании триггеров
Создание триггера
Триггеры
Триггеры – это методы, с помощью которых разработчик может обеспечить целостность БД. Другими словами, триггер – это особый вид хранимой процедуры, которая активизируется при попытке изменения данных в таблице, для которой определен триггер. Триггеры определяются для конкретных таблиц. Триггеры позволяют обеспечивать целостность данных, не полагаясь на прикладное программное обеспечение.
SQL Server применяет правила проверки и установки по умолчанию до записи информации в БД. Эти средства представляют собой предварительный фильтр для информации и могут предотвратить определенные действия по отношению к данным.
Триггер – это постфильтр, применяемый после воздействия правил и установок по умолчанию. Триггер – это специальный тип хранимой процедуры, которая выполняется после операций добавления, обновления и удаления (INSERT, UPDATE, DELETE) в данной таблице. Если триггер вызывает ошибку в запросе, SQL Server отказывается от обновления данных в таблице и возвращает сообщение об ошибке. Хотя триггер – это разновидность хранимой процедуры, его нельзя вызвать командой из программы. Он реагирует только на событие, для которого определен.
SQL Server рассматривает триггер как единое целое – как одну транзакцию, которая либо фиксируется, либо откатывается, вне зависимости от наличия соответствующих операторов в теле триггера.
Триггеры незначительно влияют на производительность сервера и часто используются для усиления приложений.
Для создания триггера необходимо быть владельцем БД и владельцем таблицы, для которой триггер создается. Создание триггера очень похоже на объявление хранимой процедуры и имеет следующий синтаксис:
CREATE TRIGGER имя_триггера
ON таблица
[WITH ENCRIPTION]
FOR [DELETE] [,][UPDATE][,][INSERT]
[WITH APPEND]
AS
{Команды SQL
|
AS
IF UPDATE (столбец_i) [AND | OR (столбец_j)…]
Команды SQL …}
· Оператор CREATE TRIGGER может применяться только к одной таблице
· Триггер можно создавать только в текущей БД, но в нем можно ссылаться на внешние объекты
· В одном операторе можно указывать несколько действий, на которые он будет реагировать (например, INSERT, DELETE).
· Триггер нельзя создавать на базе представлений.
· Нельзя выполнить триггер при обработке столбцов типа TEXT или IMAGE.
· Не следует применять инструкции SELECT, возвращающие результаты из триггера в приложение клиента.
· Нельзя использовать инструкции, изменяющие структуру БД.
Триггеры вставки и обновления
В приведенном ниже примере триггер выполняется всегда, когда в таблицу Sales вставляется строка или выполняется ее модификация. Если дата заказа не находится в пределах первых 15 дней месяца, строка в таблицу не включается.
CREATE TRIGGER tr_in
ON Sales
FOR Insert, Update
AS
DECLARE @nDay int
SELECT @nDay = DAY( i.date)
FROM Sales s, Inserted i
WHERE s.s_id = i.s_id
AND s.num = i.num
AND s.title_id = i.title_id
IF @nDay > 15
BEGIN
ROLLBACK TRAN
RAISERROR(‘Сообщение’,16,10)
END
Таблица Inserted создается автоматически при выполнении триггера. В ней содержатся строки, которые необходимо добавить в таблицу БД. Структуры таблиц Inserted и Sales совпадают.
Триггеры удаления
Триггеры удаления обычно применяются в двух случаях:
· Предотвращение удаления строк, которое может вызвать проблемы с целостностью данных.
· Выполнение каскадных операций удаления.
· Могут запрещать удаление данных ряду пользователей.
В качестве примера создадим для таблицы TAB1 триггер DELETE, который будет выводить информацию о попытках удаления, количестве удаляемых строк и запрещать эту операцию всем пользователям, кроме «dbo».
CREATE TRIGGER tr_del
ON TAB1
FOR DELETE
AS
PRINT ‘Попытка удаления ‘+STR(@@ROWCOUNT)+’ строк в таблице TAB1’
PRINT ‘Пользователь - ‘ + CURRENT_USER
IF CURRENT_USER <> ‘dbo’
BEGIN
PRINT ‘Удаление запрещено‘
ROLLBAK TRAN
END
ELSE
PRINT ‘Удаление разрешено‘