Введение
Проблема, с которой я столкнулся, довольно известна. Я имею два триггера, которые должны отработать в предопределенном порядке, т.е. триггер A должен выполниться сначала, а после него должен отработать триггер B. Вы можете поинтересоваться, а почему бы не иметь один триггер, который объединит триггеры A и B в один триггер AB? Хороший вопрос. К сожалению, триггер A используется для репликации (For Replication), в то время как более поздний триггер - не для репликации, что определяет наличие именно двух триггеров.
Давайте создадим испытательную среду. Для этого нам понадобятся две таблицы. Одна - для написания и тестирования триггеров, а вторая - для журнализации триггера и времени его выполнения.
CREATE TABLE [Trigger Priority] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[First] [int] NULL ,
[Second] [int] NULL ,
[Last] [int] NULL ,
[Status] [char] (1) NULL
) ON [PRIMARY]
GO
Один триггер будет триггером на вставку, который будет обновлять столбец First некоторым случайным числом. Этот триггер будет называться trg_UpdateFirst
CREATE TRIGGER trg_UpdateFirst ON dbo.[Trigger Priority]
FOR INSERT
AS
declare @id int, @val as float
Select @id = id from inserted
select @val = floor(rand() * 10)
Update [Trigger Priority] set [First] = @val Where ID = @id
Insert into TriggerLog (TriggerName) values ('trg_UpdateFirst')
Последняя строка триггера журнализирует имя триггера и время его срабатывания в таблице TriggerLog. Следующий триггер используется для обновления столбца Second значением из столбца First.
CREATE TRIGGER trg_UpdateSecond ON dbo.[Trigger Priority]
FOR INSERT
AS
declare @id int
Select @id = id from inserted
Update [Trigger Priority] set [Second] = [First] Where ID = @id
Insert into TriggerLog (TriggerName) values ('trg_UpdateSecond')
Последний триггер используется для обновления столбца Last суммой значений столбцов First и Second.
CREATE TRIGGER trg_UpdateLast ON dbo.[Trigger Priority]
FOR INSERT
AS
declare @id int
Select @id = id from inserted
Update [Trigger Priority] set [Last] = [First] + [Second] Where ID = @id
Insert into TriggerLog (TriggerName) values ('trg_UpdateLast')
Теперь, чтобы получить ожидаемые результаты, триггеры trg_UpdateFisrt, trg_UpdateSecond и trg_UpdateLast должны выполняться в вышеперечисленном порядке. Что вы об этом думаете? Каков будет порядок? Случайным образом или в некотором особом порядке?
Прежде чем ответить на этот вопрос, давайте посмотрим, что произойдет. После вставки одной записи в таблицу [Trigger Priority] первый столбец содержит пятерку, что нормально, и второй тоже - 5, что также правильно. Однако в последнем столбце находится NULL! Почему? Разве не должно быть 10?
Теперь давайте проверим таблицу TriggerLog. Порядок столбцов - trg_UpdateLast, trg_UpdateFirst и trg_UpdateSecond. После небольшого исследования выясняется, что триггеры выполняются в том порядке, в котором они были созданы. Таким образом, в идеале триггеры следует создавать в таком порядке: trg_UpdateFirst, trg_UpdateSecond и trg_UpdateLast. Это ни в коем случае не является простой задачей в силу динамического характера процесса разработки, который по большей части не контролируется разработчиками.
Другой вопрос. Как на более поздней стадии Вы собираетесь узнать о порядке срабатывания триггеров?
select * from sysobjects where xType ='TR' order by id
С помощью вышеупомянутого запроса Вы можете идентифицировать порядок, в котором выполнятся триггеры.
Установить порядок
Теперь вопрос о том, как мы можем задать порядок выполнения. Имеется хранимая системная процедура, которая для того и существует, чтобы ответить на подобный вопрос. Эта хранимая процедура - sp_settriggerorder. У этой SP есть три параметра.
sp_settriggerorder [@triggername =] 'triggername'
, [@order =] 'значение'
, [@stmttype =] 'statement_type'
Первый параметр - это имя триггера, а второй параметр - порядок. Этот порядок может принимать одно из трех значений: " First (первый)", "None (ни первый, ни последний)", и " Last (последний)". Последний параметр представляет собой тип триггера, т.е. Insert, Update или Delete. Это означает, что Вы не можете позволить себе иметь четыре или пять триггеров одного и того же типа, которые бы выполнялись в определенном порядке. Однако это вряд ли встречается в практике. По крайней мере, я не встречал еще так много триггеров одного типа на одной таблице.
Этот порядок не может быть установлен опциями Alter Trigger или Create Trigger. Если оператор Alter Trigger изменяет первый или последний триггер, то первоначально установленные на триггере атрибуты First или Last удаляются, и значение заменяется на None. Значение порядка должно быть переустановлено с помощью хранимой процедуры sp_settriggerorder.
Разрешения
Владелец триггера и таблицы, на которой определен триггер, имеет разрешение на выполнение sp_settriggerorder. Члены ролей db_owner и db_ddladmin в текущей базе данных, так же как серверная роль sysadmin, могут выполнять эту хранимую процедуру.
Получение порядка
Следующая проблема заключается в установлении порядка, в котором выполняются триггеры, на более поздней стадии. Если я не ошибаюсь, нет никакого прямого способа получить эту информацию из Enterprise Manager SQL Server. Вместо этого Вы пишете простые запросы.
select objectproperty(object_id(' trg_UpdateFirst '), 'ExecIsFirstInsertTrigger') ExecIsFirstInsertTrigger')
укажет, является ли trg_UpdateFirst первым (First) триггером на вставку?
Предостережения
Когда Вы генерируете скрипт для триггеров, приоритет их срабатывания не будет заскриптован. Это означает, что Вам придется повторно запустить приоритетные сценарии. Это аргумент в пользу отказа от приоритетных триггеров.
Сервер SQL 2005
В SQL Server 2005 имеется дополнительный параметр в sp_settriggerorder, который должен сообщить, является ли данный триггер триггером базы данных или триггером сервера. Это обусловлено тем, что в SQL Server 2005 Вы можете написать также DDL триггеры.
Заключение
Установка приоритетного порядка для триггера SQL Server не составляет труда. Однако Вы должны взять на себя дополнительную заботу, принимая эту возможность в структуре вашей разработки. Будет хорошо, если Microsoft может обеспечить решения для рассмотренных выше проблем.
Dinesh Asanka (оригинал: Triggers to Execute in Pre Defined Order)
Перевод: Моисеенко С.И.
Оригинал перевода