Триггеры и порядок их выполнения

by Alexey Knyazev 1. июня 2011 20:35

До версии SQL Server 7.0, мы могли создать только три триггера на одной таблице, один INSERT, один UPDATE и один DELETE. Ситуация изменилась с выходом SQL Server 7.0. Теперь мы можем на одну таблицу задать большое количество (Ограничено числом объектов в базе данных) триггеров каждого типа.

Порядок запуска триггеров при этом никак не определен и не гарантирован. В SQL Server 2000 появилась документированная процедура sp_settriggerorder, которая позволяет изменять порядок запуска триггеров. Она позволяет указать какие триггеры AFTER срабатывают первыми или последними. Триггеры AFTER, срабатывающие между первым и последним триггерами, выполняются в неопределенном порядке.

Для демонстрации запустим скрипт:

--Тестовая таблица
if object_id( 'dbo.TestTable', 'U' ) is not null
  drop table dbo.TestTable
go
create table dbo.TestTable ( id int identity, val varchar(64) )
go

--Триггер 1
if object_id ( 'dbo.TestTrigger01', 'TR' ) is not null
  drop trigger dbo.TestTrigger01
go
create trigger dbo.TestTrigger01 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 2
if object_id ( 'dbo.TestTrigger02', 'TR' ) is not null
  drop trigger dbo.TestTrigger02
go
create trigger dbo.TestTrigger02 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 3
if object_id ( 'dbo.TestTrigger03', 'TR' ) is not null
  drop trigger dbo.TestTrigger03
go
create trigger dbo.TestTrigger03 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 4
if object_id ( 'dbo.TestTrigger04', 'TR' ) is not null
  drop trigger dbo.TestTrigger04
go
create trigger dbo.TestTrigger04 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 5
if object_id ( 'dbo.TestTrigger05', 'TR' ) is not null
  drop trigger dbo.TestTrigger05
go
create trigger dbo.TestTrigger05 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

Мы создали тестовую таблицу с 5-ю триггерами AFTER на событие INSERT. Каждый из триггеров добавляет запись в эту же таблицу. Теперь проведем 1ый тест, выполнив следующий скрипт:
--Тест 1: Порядок запуска триггеров не задан
insert into dbo.TestTable
select 'Test1'
go
select * from dbo.TestTable
order by id
go



На картинке видно, что триггеры отработали исходя из времени своего создания, т.е. триггер, который создан раньше, отработал так же раньше других. Для подтверждения этого, пересоздадим триггер 2 и проведем тестовую вставку строки в нашу таблицу:
--Тест 2: Порядок запуска триггеров не задан, триггер №2 пересоздадим, чтобы он стал самым поздним по времени создания
--Триггер 2
if object_id ( 'dbo.TestTrigger02', 'TR' ) is not null
  drop trigger dbo.TestTrigger02
go
create trigger dbo.TestTrigger02 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go
insert into dbo.TestTable
select 'Test2'
go
select * from dbo.TestTable
order by id
go



Теория подтвердилась, но это всего лишь совпадение, и такое поведение не гарантировано и, как указано в БОЛ, без явного указания порядка, триггеры выполняются случайным образом. Но через процедуру sp_settriggerorder мы можем задать только триггеры AFTER, срабатывающие первыми или последними. Триггеры AFTER, срабатывающие между первым и последним триггерами, выполняются в неопределенном порядке. Т.е. если у нас 3 триггера на одно событие, то мы сможем гарантировать порядок срабатывания, через эту процедуру. Но если триггеров больше, как в нашем случае, то порядок будет гарантирован только для первого и последнего триггера. Для иллюстрации работы процедуры sp_settriggerorder, укажем в качестве первого триггера Триггер 2, а последним Триггер 3:
--Тест 3: Зададим порядок запуска триггеров 2 и 3
exec sp_settriggerorder 'dbo.TestTrigger02', 'first', 'insert'
go
exec sp_settriggerorder 'dbo.TestTrigger03', 'last', 'insert'
go
insert into dbo.TestTable
select 'Test3'
go
select * from dbo.TestTable
order by id
go



При попытке задать для другого триггера порядок выполнения, например первым (first), мы получим ошибку:
Msg 15130, Level 16, State 1, Procedure sp_settriggerorder, Line 163
There already exists a 'first' trigger for 'insert'.


Просмотреть, задан ли порядок запуска для триггера можно с помощью функции OBJECTPROPERTY
select name
     , objectproperty( object_id, 'ExecIsFirstInsertTrigger') FirstInsertTrigger 
     , objectproperty( object_id, 'ExecIsLastInsertTrigger')  LastInsertTrigger 
from sys.triggers
where name like 'TestTrigger0%'



На что ещё стоит обратить внимание: Если один и тот же триггер необходимо назначить в качестве первого или последнего для нескольких типов инструкций, процедуру sp_settriggerorder необходимо выполнить для каждого типа инструкций. Кроме того, прежде чем триггер может быть назначен в качестве первого (First) или последнего (Last) выполняющегося триггера для того или иного типа инструкций, этот триггер должен быть сначала определен для данного типа.

Ну и последний тест, сбросим порядок запуска триггеров 2 и 3:
--Тест 4: Сбросим порядок запуска триггеров 2 и 3
exec sp_settriggerorder 'dbo.TestTrigger02', 'none', 'insert'
go
exec sp_settriggerorder 'dbo.TestTrigger03', 'none', 'insert'
go
insert into dbo.TestTable
select 'Test4'
go
select * from dbo.TestTable
order by id
go



Триггеры были запущены в том же порядке, что и до явного выставления порядка у триггеров 2 и 3.

Tags:

SQL Server

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading