Триггеры

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

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

· Сравнивать предшествующие и новые версии данных. В большинстве случаев в момент выполнения триггера необходимо знать, какие изменения были выполнены исходным оператором модификации данных. Сведения об этом можно найти в таблицах inserted (вставленные) и deleted (удаленные), которые становятся доступны внутри триггера. Эти таблицы – фактически представления строк в файле регистрации транзакции, которые были изменены оператором и имеют структуры и имена столбцов, идентичные таблице, которая изменилась. Таким образом, оценить, какие именно изменения были произведены в таблице, можно исследуя содержимое таблиц inserted и deleted, как показано в следующей таблице, и соответственно предпринимать те или иные действия в зависимости от обнаруженных различий.

Оператор	Содержимое таблицы inserted	Cодержимое таблицы deleted
INSERT	Добавленные строки	Пусто
UPDATE	Новые строки	Старые строки
DELETE	Пусто	Удаленные строки

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

· Осуществлять считывание из других таблиц.

· Изменять другие таблицы.

· Выполнять хранимые процедуры и функции.

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

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

Триггеры также используют в более сложной и гибкой форме ограничений. Ограничения лимитированы только пределами одной таблицы, в то время как триггеры потенциально имеют доступ ко всей базе данных. Предположим, что необходимо разрешить принимать заказы только от тех клиентов, которые не имеют задолженностей по уже имеющимся счетам. В этом случае можно создать триггер на вставку, который проверяет данное правило и в случае необходимости отменяет исходную операцию вставки (откатывает транзакцию) с выбросом соответствующего сообщения об ошибке.

Общий синтаксис запроса на создания триггера выглядит следующим образом:

CREATE TRIGGER имя_триггера
ON имя_таблицы
AFTER { [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }
AS
{ инструкции T-SQL }

При создании триггера указывается его имя, имя таблицы, для которой он определяется, и события, при возникновении которых он должен выполняться (перечисление после ключевого слова AFTER). Эти события соответствуют трем операциям модификации данных: вставка, обновление и удаление. При этом триггер запускается один раз на каждую операцию модификации данных вне зависимости от количества затронутых этой операцией записей.

Определим в таблице City триггер, отслеживающий все изменения в данной таблице. Для хранения информации обо всех операциях модификации, выполняемых над данными в этой таблице, создадим специальную таблицу sysCityAudit со следующими столбцами: IdOperation (уникальный идентификатор операции), TypeOp (тип операции: вставка, обновление или удаление), IdCity, CityName, DateAndTime (дата и время выполнения операции), UserName (имя пользователя, изменившего данные). Запрос на создание данной таблицы приведен ниже:

CREATE TABLE [dbo].[sysCityAudit](

[IdOperation] [int] IDENTITY(1,1) NOT NULL,

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

[IdCity] [int] NOT NULL,

[CityName] [nvarchar](20) NULL,

[DateAndTime] [datetime] NOT NULL CONSTRAINT [DF_sysCityAudit_DateAndTime] DEFAULT (getdate()),

[UserName] [nvarchar](256) NOT NULL CONSTRAINT [DF_sysCityAudit_UserName] DEFAULT (user_name()),

CONSTRAINT [PK_sysCityAudit] PRIMARY KEY CLUSTERED

(

[IdOperation] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Запрос на создание триггера, отслеживающего все изменения в таблице City:

CREATE TRIGGER [dbo].[tr_CityAudit] ON [dbo].[City]

AFTER INSERT,DELETE,UPDATE

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)

INSERT sysCityAudit(TypeOp,IdCity,CityName)

SELECT 'Обновление', IdCity, CityName

FROM inserted

ELSE IF EXISTS(SELECT * FROM inserted) --

INSERT sysCityAudit(TypeOp,IdCity,CityName)

SELECT 'Вставка', IdCity, CityName

FROM inserted

ELSE

INSERT sysCityAudit(TypeOp,IdCity,CityName)

SELECT 'Удаление', IdCity, CityName

FROM deleted

END

Проверьте работоспособность вновь созданного триггера. Для этого произведите в таблице City различные изменения и убедитесь, что подробная информация о них была записана в таблицу sysCityAudit.

Для демонстрации применения триггера, в качестве инструмента ограничивающего возможные операции с данными в таблице в соответствии с определенными бизнес-правилами, создадим триггер, запрещающий оформлять заказы от клиентов из Москвы (к базе данных Sales сложно сформулировать осмысленное бизнес-правило, требующее использование триггера, поэтому пример весьма искусственный).

CREATE TRIGGER [dbo].[tr_OrderConstraint] ON [dbo].[Order]

AFTER INSERT,UPDATE

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT *

FROM inserted AS i INNER JOIN

Customer AS cust ON i.IdCust = cust.IdCust INNER JOIN

City AS c ON cust.IdCity = c.IdCity

WHERE c.CityName = N'Москва')

BEGIN

ROLLBACK TRANSACTION

RAISERROR('Оформление заказов для клиентов из Москвы запрещено',16,1)

END

END

Поскольку триггер выполняется в рамках транзакции соответствующей ей операции модификации данных для отмены самой операции достаточно выполнить команду ROLLBACK TRANSACTION. Кроме того в случае попытки нарушения заданного бизнес-правила желательно с помощью команды RAISEERROR отправить пользователю сообщение об ошибке c ее подробным описание.

Задание для самостоятельной работы: Создайте триггер, запрещающий добавлять в заказ товары, отсутствующие на складе.

Дата добавления: 2014-12-08