Создание и использование триггеров

Использование триггеров AFTER

Как уже говорилось ранее в этой лекции, триггер AFTER – это просто триггер, который активизируется после завершения указанного события модификации данных. Если у вас определен по таблице более чем один триггер AFTER для определенного события или набора событий, то вы можете задать, какой триггер будет активизироваться первым и какой – последним. Любые другие триггеры, определенные по данной таблице для этого события или набора событий, будут активизироваться в случайном порядке. Первый и последний триггеры задаются с помощью оператора T-SQL sp_settriggerorder. Этот оператор имеет следующий синтаксис:

sp_settriggerorder   [@triggername =] 'имя_триггера',
                             [@order=] {'first' | 'last' | 'none'}

Рассмотрим один пример. Предположим, что у нас четыре триггера, определенных по таблице: MyTrigger, MyOtherTrigger, AnotherTrigger и YetAnotherTrigger. Мы хотим убедиться, что после запускающего события AnotherTrigger активизируется первым и MyTrigger активизируется последним. Мы вводим следующие операторы T-SQL:

sp_settriggerorder @triggername = 'AnotherTrigger', @order = 'first'
go
sp_settriggerorder @triggername = 'MyTrigger', @order = 'last'
go
sp_settriggerorder @triggername = 'MyOtherTrigger', @order = 'none'
go
sp_settriggerorder @triggername = 'YetAnotherTrigger', @order = 'none'
go

Обозначение "none" для MyOtherTrigger и YetAnotherTrigger указывает, что SQL Server должен активизировать эти триггеры случайным образом после активизации AnotherTrigger и до активизации MyTrigger. Поскольку этот случайный порядок активизации принят для триггеров по умолчанию, то вы не обязаны явно запускать оператор sp_settriggerorder для триггеров, которые запускаются случайным образом.

Использование вложенных триггеров

Вложенные триггеры – это триггеры, которые активизируются другими триггерами. Они отличаются от рекурсивных триггеров, которые активизируют сами себя. Вложенный триггер инициируется, когда событие модификации данных внутри другого триггера активизирует этот вложенный триггер. Как и в SQL Server 7, SQL Server 2000 допускает до 32 уровней вложенности триггеров. Один триггер активизирует второй триггер, который, в свою очередь, активизирует третий триггер, и т.д. вплоть до 32-го триггера. Использование вложенных триггеров разрешается в SQL Server 2000 по умолчанию. Вы можете указать, разрешается ли в SQL Server использование вложенных триггеров, задав соответствующий параметр конфигурации сервера для вложенных триггеров Например, чтобы блокировать использование вложенных триггеров, выполните следующую команду:

sp_configure "nested triggers", 0
GO

Значение 0 блокирует использование вложенных триггеров; значение 1 разрешает их использование. Рассмотрим пример использования вложенных триггеров. В этом примере мы создадим вложенные триггеры, которые будут выполнять каскадные удаления при удалении заголовка книги из таблицы titles. (См. раздел "Создание триггера типа DELETE" ранее.) Мы создали отдельный триггер, который выполняет эту операцию. Сначала мы удалим этот триггер, чтобы он не активизировался. Затем мы создадим три триггера. Второй и третий триггеры будут вложенными триггерами. Первый триггер будет активизировать второй триггер, который будет активизировать третий триггер. Вот эта программа:

USE pubs
GO 
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = "Delete_Title" AND 
                              type = "TR") 
DROP TRIGGER Delete_Title 
GO 
 
CREATE TRIGGER TR_on_titles 
ON titles 
FOR DELETE 
AS 
DELETE   sales 
FROM     sales, deleted 
WHERE    sales.title_id = deleted.title_id 
PRINT    "Deleted from sales" 
GO 

CREATE TRIGGER TR_on_sales 
ON sales 
DELETE   roysched 
FROM     roysched, deleted 
WHERE    roysched.title_id = deleted.title_id 
PRINT    "Deleted from roysched" 
GO 
 
CREATE TRIGGER TR_on_roysched 
ON roysched 
DELETE   titleauthor 
FROM     titleauthor, deleted 
WHERE    titleauthor.title_id = deleted.title_id 
PRINT    "Deleted from titleauthor" 
GO

Для успешной работы этих триггеров вы должны удалить ограничения FOREIGN KEY по таблицам, указанным в этих триггерах (см. раздел "Создание триггера типа DELETE" ранее). Чтобы проверить, работают ли все эти триггеры, запустите следующий оператор DELETE:

DELETE
FROM     titles 
WHERE    title_id = "PS7777" 
GO

Вы увидите следующий набор результатов:

(2 row(s) affected)  

(1 row(s) affected)  

Deleted from titleauthor     (Удалено из titleauthor)

(2 row(s) affected)

Deleted from roysched    

(Удалено из roysched)

(1 row(s) affected)

Deleted from sales             (Удалено из sales)

(1 row(s) affected)

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