Which trigger will be triggered first?

Hi Friends,

Yesterday I was working on a specific requirement of a client to create DDL trigger for a Database to log & validate certain things. While doing so I discovered there were already two DDL triggers created on the DB for the same event. Now I was not sure which one will be triggered first and after searching msdn I came across a stored procedure which can control this behaviour to certain extent.

SP_SetTriggerOrder, this is a system stored procedure which can specify the after triggers that should be fired at First or Last order. Unfortunately you can’t control the complete sequence but this much is more than enough to handle most of the requirements.

This system stored procedure accepts four parameters namely:

TriggerName: Trigger Name, you should mention schema name wherever applicable. However as stated earlier also this will not accept InsteadOf triggers.

Order: This parameter can accept one of these three values (‘First’, ‘Last’, ’None’) you can specify None if you don’t wish to set any sequence for this trigger.

Stmttype (StatementType): you can specify here the EventType for which the trigger has been created. You can’t specify an EventGroup.

NameSpace: This parameter describes the scope of trigger, the valid values are (‘Database’, ‘Server’, ‘NULL’) to define Database Scope, Server Scope and DML Trigger respectively.

Here is an example where I am configuring the zone_target Stored procedure to be the last fired trigger for Insert Statement

Exec sp_settriggerorder

@triggername= ‘sales.zone_target’,

@order=’Last’,

@stmttype = ‘Insert’;

Caveat –there are a few things you need to consider before using this SP.

1. Replication automatically generates a First trigger for the tables if an immediate update or Queued updating subscription is configured, so in case you’ve defined a first trigger for a table you’ll not be able to add it in replication. To make it work either alter the replication trigger or change the sequence for other trigger on the object.
2. There can be only one First and one Last trigger for each statement on a single table.
3. If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset by using sp_settriggerorder.
4. If the same trigger must be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for that statement type.
5. The order of execution of DDL triggers that exist on the same event is as follows:

• The server-level trigger marked First.
• Other server-level triggers.
• The server-level trigger marked Last.
• The database-level trigger marked First.
• Other database-level triggers.
• The database-level trigger marked Last.

Hope this will help you, do leave us comments & let us know how we are doing.

 

 

Regards

Sarabpreet Anand