Триггеры

Триггер – это хранимая процедура специального типа, автоматически выполняющаяся при наступлении заданного события. Триггеры не содержат явных входных или выходных параметров и не могут быть запущены явным образом (по команде EXEC). SQL Server поддерживает три класса триггеров: триггеры языка обработки данных (триггеры DML), триггеры языка описания данных (триггеры DDL) и триггеры входа в систему.

Триггеры DML запускаются при попытке изменить данные в таблице или представлении с помощью операторов INSERT, UPDATE или DELETE. Формат команды, создающей триггер, следующий:

CREATE TRIGGER [schema_name.]trigger_name

ON {table | view]

[WITH <option> [ ,...n ] ]

{FOR |AFTER |INSTEAD OF]

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

[NOT FOR REPLICATION]

AS {sql_statement [ ; ] [ ,...n ]}

<option>::= {[ENCRYPTION]! [EXECUTE AS Clause]}

Здесь необходимы следующие пояснения. Тип триггера AFTER означает, что он срабатывает только после успешного выполнения оператора, запускающего триггер. При этом сначала будут выполнены все проверки ограничений, и если они пройдут успешно, сработает триггер. Например, если

добавление записи в таблицу невозможно из-за повторяющегося значения первичного ключа, то связанный с этой операцией триггер типа AFTER не запустится.

Если указано только ключевое слово FOR, это указывает на создание триггера AFTER, т.е. FOR и AFTER в данном случае – синонимы. При этом надо учитывать, что операторы INSERT, UPDATE, DELETE выполняются в рамках неявной транзакции. Триггер запускается в рамках этой же транзакции, и из него транзакцию можно целиком откатить. Триггеры AFTER не могут быть определены для представлений [23].

Триггер типа instead of означает, что действия оператора, вызвавшего триггер, не выполняются, а вместо этого исполняется код триггера. В таблице или представлении для каждого из операторов INSERT, UPDATE или DELETE может быть определено не более одного триггера типа INSTEAD OF. Однако для таблицы можно создать несколько представлений, у каждого из которых может быть собственный триггер INSTEAD OF. Использование триггеров INSTEAD OF не допускается в поддерживающих обновление представлениях, которые используют параметр WITH CHECK OPTION (см. параграф 10.7).

Перечисление " [ INSERT] [, ] [UPDATE] [, ] [DELETE] " указывает оператор или операторы, выполнение которых приведет к запуску создаваемого триггера.

Опции триггера ENCRYPTION и EXECUTE AS имеют тот же смысл, что и для хранимых процедур (см. параграф 10.3). Параметр NOT FOR REPLICATION управляет поведением триггера при внесении изменений механизмом репликации. По умолчанию действия по модификации данных (UPDATE, DELETE, INSERT), выполняемые механизмом репликации, вызывают запуск соответствующего триггера. Однако если триггер создан с указанием этого параметра, в описанных случаях триггер запускаться не будет [20].

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

Таблица 10.7

Содержимое специальных таблиц, доступных триггерам DML

Оператор

Таблица

Содержимое таблицы

INSERT

inserted

Вставленные в таблицу новые строки

DELETE

deleted

Удаленные строки

UPDATE

inserted

Новое состояние измененных строк

deleted

Старое состояние измененных строк

С помощью триггеров можно реализовать проверку сложных логических условий, выполнение которых обеспечивает непротиворечивость данных. Например, нужно при внесении какой-то записи в таблицу проверить наличие связанных данных в другой таблице, находящейся в другой БД. Ограничение внешнего ключа здесь не поможет, так как БД разные, а с помощью триггера такую проверку выполнить можно.

Рассмотрим следующий пример. В БД MyTest имеется таблица Bookl, уже использовавшаяся в примерах. В БД MyTest2 имеется таблица BlackList, созданная с помощью приведенного ниже скрипта:

USE [MyTest2]

GO

CREATE TABLE [dbo].[BlackList](

[Id] [int] primary key,

[Author] [varchar] (50) NOT NULL,

[Title] [varchar] (50) NOT NULL)

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

use MyTest

go

CREATE TRIGGER CheckBlackList

ON dbo.Bookl

AFTER INSERT, UPDATE

AS BEGIN

IF EXISTS

(SELECT * FROM [MyTest2].[dbo].[BlackList] b

INNER JOIN inserted i ON

  • ( (b. [Author] = i. [Author]) AND
  • (b.[Title]=i.[Title])))

BEGIN

RAISERROR ('Одна из книг в таблице BlackList, добавление невозможно',10,1);

ROLLBACK TRANSACTION END END

Если хотя бы одна из добавляемых книг оказалась в таблице BlackList, триггером инициируется ошибка и производится откат транзакции. Ошибка генерируется системной функцией RAISERROR, первый аргумент которой – сообщение об ошибке, второй – уровень серьезности ошибки (англ. severity), третий – состояние (англ. state). Не разбирая подробно возможности функции, отметим, что задавать ошибки с уровнем серьезности от 0 до 18 может любой пользователь, от 19 до 25 – только администратор.

Пусть таблица BlackList содержит следующие записи (табл. 10.8), а в таблице Bookl уже есть записи со значением поля Id (первичный ключ), равным 1, 2, 3 и 4.

Таблица 10.8

Содержимое таблицы Blacklist

Id

Author

Title

1

Автор1

Плохой учебник1

2

Автор2

Плохой учебник2

Рассмотрим три команды обновления данных и получаемый результат: INSERT INTO dbo.Bookl(Id,Author,Title,Publisher,[Year])

VALUES (','ABTopl', 'Плохой учебник1', 'Издательство1',

2012);

Запись добавлена не будет, но не из-за триггера, а из-за совпадения первичных ключей. Ограничение первичного ключа проверяется до запуска триггера AFTER. Если оно не выполняется, то и триггер запущен не будет. INSERT INTO dbo.Bookl(Id,Author,Title,Publisher,[Year])

VALUES (','ABTopl', 'Плохой учебник1', 'Издательство1', 2012);

Попытка добавления записи будет неуспешной: триггер после проверки 5'словия инициирует ошибку и выполнит откат транзакции.

INSERT INTO dbo.Bookl(Id,Author,Title,Publisher,[Year])

VALUES (','ABTopl', 'Плохой учебник1', 'Издательство1', 2012), (6,'Автор1', 'Хороший учебник', 'Издательство1', 2013);

Здесь одним оператором INSERT в таблицу Bookl добавляются две записи, причем одна книга находится в "черном списке", а вторая – нет. Но триггер выполняет откат всей транзакции, поэтому ни одна из книг добавлена не будет. В связи с последним примером хочется отметить, что рекомендуется писать триггеры таким образом, чтобы они корректно работали в любой ситуации: и если была изменена только одна строка, и если изменены были несколько строк.

Перейдем теперь к триггерам DDL. Они создаются для отдельной БД или целиком экземпляра SQL Server и запускаются по событиям, связанным с изменением объектов БД, например при попытке удаления таблицы: CREATE TRIGGER trigger_name

ON (ALL SERVER | DATABASE)

[ WITH <option> [ ,...n ] ]

(FOR I AFTER] (event_type |event_group ) [ ,...n ]

AS ( sql_statement [ ; ] [ ,...n ] | )

Ключевое слово DATABASE указывает на то, что триггер создается для текущей БД, ALL SERVER целиком для сервера. Задаваемые в секции WITH опции аналогичны тем, что были для триггеров DML. Типы событий полностью описаны в документации SQL Server, некоторые часто используемые приведены в табл. 10.9 [20]. Вместо типа события может быть указана группа событий, и триггер будет вызваться по каждому событию из этой группы. Например, группа DDL GDR DATABASE EVENTS включает события, связанные с управлением разрешениями на уровне БД:

  • • DENY DATABASE (запрещение действия);
  • • GRANT DATABASE (предоставление права на выполнение действия);
  • • REVOKE DATABASE (отзыв права на выполнение действия).

Таблица 10.9

Примеры событий для триггеров DDL

Оператор DDL

Тип события

Описание

CREATE DATABASE

CREATE_DATABASE

Создание БД

DROP LOGIN

DROP_LOGIN

Удаление учетной записи

UPDATE STATISTICS

UPDATE_STATISTICS

Обновление статистики

DROP TRIGGER

DROP_TRIGGER

Удаление триггера

ALTER TABLE

ALTER_TABLE

Изменение таблицы

Рассмотрим теперь пример триггера DDL. Он предотвращает случайное удаление таблицы – при попытке выполнить подобную операцию выводится сообщение, и транзакция откатывается:

Use MyTest2 go

CREATE TRIGGER NoDropTab

ON DATABASE

FOR DROP_TABLE

AS

Print N'Вы пытаетесь удалить нужную таблицу!';

Print N'Если вы уверены в своих действиях, сначала отключите этот триггер.';

ROLLBACK;

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

Use MyTest2

go

DROP TABLE dbo.BlackList; – удаление не выполнить

Временно отключить триггер можно командой DISABLE TRIGGER, где указываются имя триггера и объект, для которого он определен. Снова включить триггер можно по команде ENABLE TRIGGER. Например, если таблицу в базе MyTest2 все же надо удалить, это можно сделать следующим образом: Use MyTest2

go

DISABLE TRIGGER NoDropTab ON DATABASE;

DROP TABLE dbo.BlackList;

ENABLE TRIGGER NoDropTab ON DATABASE;

Перейдем к рассмотрению третьей категории триггеров – триггеров входа (англ. logon trigger). Такой триггер запускается после успешной аутентификации, но до того, как устанавливается сеанс работы пользова

теля. Формат оператора создания триггера приведен ниже, опции в секции WITH – такие же, как в предыдущем случае:

CREATE TRIGGER trigger_name

ON ALL SERVER

[ WITH <option> [ ,...n ] ]

{ FORI AFTER } LOGON

AS { sql_statement [ ; ] [ ,...n ] }

Рассмотрим следующий пример из справочной системы MSDN [23], иллюстрирующий применение триггера входа. Сначала создается учетная запись login_test и ей предоставляются права на просмотр информации о состоянии сервера:

USE master;

GO

CREATE LOGIN login_test WITH PASSWORD = ' 3KHJ6dhx (OxVYsdf ' MUST_CHANGE,

CHECK_EXPIRATION = ON;

GO

GRANT VIEW SERVER STATE TO login_test;

Далее на сервере создается триггер входа connection_limit_ trigger, исполняющийся в контексте безопасности записи login_test. Триггер запрещает попытку подключения к SQL Server с учетной записью login_test, если от ее имени уже установлено три сеанса:

CREATE TRIGGER connection_limit_trigger

ON ALL SERVER WITH EXECUTE AS 'login_test'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= ' login_test' AND

(SELECT COUNT(*) FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'login_test') > 3

ROLLBACK-

END;

Используемое в коде триггера системное представление sys. dm_exec_ sessions возвращает по одной строке на каждый прошедший аутентификацию сеанс, подключенный к SQL Server. Его столбец original_ login_name содержит имя учетной записи, а столбец is_user_process содержит 0, если сеанс является системным, и 1 – в противном случае. Функция ORIGINAL_LOGIN() позволяет получить имя учетной записи, которая устанавливает сеанс, запустивший триггер.

Удаление триггера выполняется оператором DROP TRIGGER. Его формат будет несколько отличаться в зависимости от типа триггера:

– для триггера DML

DROP TRIGGER [schema_name.]trigger_name [ ,...n ] [ ;

]

– для триггера DDL

DROP TRIGGER trigger_name [ ,...n ]

ON { DATABASE I ALL SERVER } [ ; ]

– для триггера входа

DROP TRIGGER trigger_name [ ,...n ]

ON ALL SERVER [ ; ]

В качестве примера выполним удаление созданного ранее в таблице Bookl БД MyTest триггера CheckBlackList:

use MyTest

go

DROP TRIGGER dbo.CheckBlackList

Изменить существующий триггер можно с помощью оператора ALTER TRIGGER. Кроме ключевого слова ALTER, формат оператора такой же, как при создании триггера.

 
< Пред   СОДЕРЖАНИЕ     След >