Performance of triggers in the certain order

Introduction

The problem which I have collided{faced}, is rather known. I have two triggers which should fulfil in the predetermined order, i.e. the trigger a should be executed all over again, and after him{it} should fulfil the trigger b. You can take an interest and why to not have one trigger which will unit triggers a and b in one trigger ab? A good question. Unfortunately, the trigger a is used for replikacii (for replication) while later trigger - not for replikacii, that defines{determines} presence of two triggers.

Let's create the test environment. Two tables will be necessary for this purpose for us. One - for a spelling and testing of triggers, and the second - for journalizing the trigger and time of his{its} performance.

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

One trigger will be the trigger on an insert which will update a column first some random number. This trigger will be called 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 ')

Last line of the trigger zhurnaliziruet a name of the trigger and time of his{its} operation in the table triggerlog. The following trigger is used for updating a column second by value from a column 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 trigger is used for updating a column last by the sum of values stolbcov first and 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 ')

Now, to receive expected results, triggers trg_updatefisrt, trg_updatesecond and trg_updatelast should be carried out in the set forth above order. What do you think of it? What order will be? In the casual image or in some special order?

Before to answer this question, let's see, that will take place. After an insert of one recording in the table [trigger priority] the first column contains the five that is normal, and the second too - 5, that also is correct. However in the last stolbce is null! Why? Unless should not be 10?

Now let's check up the table triggerlog. The order stolbcov - trg_updatelast, trg_updatefirst and trg_updatesecond. After small research it is found out, that triggers are carried out in that order in which they have been created. Thus, in an ideal triggers should be created in such order: trg_updatefirst, trg_updatesecond and trg_updatelast. It at all is not a simple problem{task} by virtue of dynamic character of process of development which generally is not supervised by developers.

Other question. How at later stage you are going to to learn{find out} about the order of operation of triggers?

select * from sysobjects where xtype = ' tr ' order by id

With the help of the above mentioned search you can identify the order in which triggers will be executed.

To establish the order

Now a question on how we can set the order of performance. There is a stored{kept} system procedure, which for that and exists to answer a similar question. This stored{kept} procedure - sp_settriggerorder. This sp has three parameters.

sp_settriggerorder [@triggername =] ' triggername '

, [@order =] ' value '

, [@stmttype =] ' statement_type '

The first parameter is a name of the trigger, and the second parameter - the order. This order can accept one of three values: " first (first) ", " none (neither the first, nor last) ", and " last (last) ". Last parameter represents type of the trigger, i.e. insert, update or delete. It means, that you do not presume to have to yourselves four or five triggers of the same type which would be carried out in the certain order. However it hardly meets in practice. At least, I did not meet still so many triggers of one type on one table.

This order cannot be established by options alter trigger or create trigger. If the operator alter trigger changes first or last trigger attributes originally established by the trigger first or last leave, and value is replaced on none. Value of the order should be reinstalled with the help of stored{kept} procedure sp_settriggerorder.

Sanctions

The owner of the trigger and the table on which the trigger is determined, has the sanction to performance sp_settriggerorder. Members of roles db_owner and db_ddladmin in the current database just as a server role sysadmin, can carry out this stored{kept} procedure.

Reception of the order

The following problem consists in an establishment of the order in which triggers are carried out, at later stage. If I am not mistaken, there is no direct way to receive this information from enterprise manager sql server. Instead of it you write simple searches.

select objectproperty (object_id (' trg_updatefirst ')', execisfirstinserttrigger ') execisfirstinserttrigger)

Will specify, whether is trg_updatefirst the first (first) the trigger on an insert?

Cautions

When you generate a script for triggers, the priority of their operation will not be zaskriptovan. It means, that you should start priority scripts repeatedly. It is argument for the benefit of refusal of priority triggers.

Sql 2005 server

In sql server 2005 there is an additional parameter in sp_settriggerorder which should inform, whether the given trigger the trigger of a database or the trigger of the server is. It is caused by that, that in sql server 2005 you can write also ddl triggers.

The conclusion

Installation of the priority order for the trigger sql server is possible. However you should undertake additional care, accepting this opportunity in structure of your development. It will be good, if microsoft can provide decisions for the problems considered above.