Триггеры. Триггеры DML. Триггер FOR (либо AFTER). Триггер INSTEAD OF. Триггеры DDL. Использование триггера DDL базы данных

Триггер — это хранимая процедура специального типа, запускаемая автоматически, когда выполняется связанная с ним инструкция языка DML или DDL. 

v  Триггеры DML выполняются, когда вы добавляете, изменяете или удаляете строки в таблице. 

v  Триггеры DDL выполняются, когда выполняются команды DDL или пользователи регистрируются в экземпляре SQL Server.

3.1.          Триггеры DML  

Несмотря на то, что триггер — программируемый объект, создаваемый вами, выполнить его напрямую вы не сможете. Триггеры DML обрабатывают таблицу или представление и задаются для определенного события: INSERT, UPDATE ИЛИ DELETE. Когда вы инициируете событие, для которого задан триггер, SQL Server автоматически выполняет программный код триггера или, как говорят, "запускает" триггер.

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

Триггер FOR (либо AFTER) 

Триггер AFTER выполняется только после того, как вызывающая срабатывание триггера инструкция SQL была успешно выполнена. 

Успешное выполнение также подразумевает завершение всех ссылочных каскадных действий и проверки ограничений, связанных с измененными или удаленными объектами. 

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

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

Пример 3.1.

Следующий триггер DML формирует сообщение, когда кто-то добавляет или изменяет данные в таблице Customer.

 

Пример 3.2.

рСледующий триггер выводит информацию о попытках удаления и количестве удаляемых строк из таблицы PRODUCT  

CREATE TRIGGER no_delete_product ON PRODUCT FOR DELETE

 AS 

 PRINT 'Попытка удаления ' +str(@@rowcount) + 

 ' строк в таблице PRODUCT'

 PRINT 'Пользователь ' + user_name( )

 IF user_name()<> 'dbo'

 BEGIN

                              PRINT char(10)+ char(13)+'УДАЛЕНИЕ ЗАПРЕЩЕНО'

                              ROLLBACK  TRANSACTION

 END

 ELSE

                              PRINT char(10)+ char(13)+'УДАЛЕНИЕ РАЗРЕШЕНО'

Пример 3.3.

Ниже представлен простой триггер, который запрещает обновлять значение столбца price для таблицы PRODUCT. При этом с помощью функции RAISERROR будет возвращено сообщение о причине отказа. 

CREATE TRIGGER [NO_UPDATE] ON [dbo].[PRODUCT] FOR UPDATE

AS

 IF UPDATE (price)

 BEGIN

                                RAISERROR ('ОБНОВЛЯТЬ НЕЛЬЗЯ', 15,1)

                                ROLLBACK TRANSACTION

 END

Триггер INSTEAD OF

Триггер, определенный с предложением INSTEADOF, вызывает выполнение программного кода триггера вместо инструкций INSERT, UPDATE ИЛИ DELETE.  

На каждую инструкцию INSERT, UPDATE ИЛИ DELETE в таблице или представлении может быть определено не более одного триггера INSTEADOF. Однако можно определить представления на представлениях, где у каждого представления есть собственный триггер INSTEADOF.  

Триггеры DML часто используются для соблюдения бизнес-правил и целостности данных. 

Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности в инструкциях ALTER TABLE и CREATE TABLE используются ограничения PRIMARY KEY и FOREIGN KEY. 

v  Если ограничения распространяются на таблицу триггера, они проверяются после срабатывания триггера INSTEAD OF и до выполнения триггера AFTER. 

v  В случае нарушения ограничения выполняется откат действий триггера INSTEAD OF, а триггер AFTER не срабатывает.

Во время выполнения триггера доступны две специальные таблицы, именуемые INSERTED  и DELETED. 

Пример 3.4.

Вместо использования ограничения FOREIGN KEY (FK_GOODS_CATEGORY) для таблицы GOODS создать триггер удаления для таблицы CATEGORY. Структура БД GOODSTURN предполагает, что нельзя удалить категорию, если по данной категории существует товар.

USE GOODSTURN;

GO

CREATE TRIGGER TD_CATEGORY ON adm.CATEGORY FOR DELETE AS

BEGIN

  DECLARE  @ERRNO   INT,

           @ERRMSG  VARCHAR(255)

IF EXISTS (SELECT * FROM DELETED,GOODS

      WHERE GOODS.CATEGORY = DELETED.CATEGORY)

    BEGIN

      SELECT @ERRNO  = 50001,

             @ERRMSG = 'СУЩЕСТВУЕТ ТОВАР УДАЛЯЕМОЙ КАТЕГОРИИ.'

      GOTO ERROR

    END

RETURN ERROR:

    RAISERROR @ERRNO @ERRMSG

    ROLLBACK TRANSACTION END GO

3.2.          Триггеры DDL  

Триггеры DDL срабатывают в ответ на ряд событий языка определения данных (DDL). Эти события, прежде всего, соответствуют инструкциям CREATE, ALTER, DROP. 

У триггера DDL следующий общий вид синтаксической записи