Tags

,


If there is more than one trigger on a table, then if needed we can set the execution order for those triggers. SQL Server has this support from SQL Server 2000. With my experience I know, in some cases setting the execution order of Triggers defined on same tables becomes necessary.

We can set the order of triggers by using a system stored procedure: sp_settriggerorder.

Using sp_Settriggerorder stored procedure, we can define the execution order of the AFTER triggers. Here is the syntax for SQL Server 2005, taken from BOL. For complete explanation of syntax, please refer BOL.

sp_settriggerorder [ @triggername= ] '[ triggerschema. ] triggername'
    , [ @order= ] 'value'
    , [ @stmttype= ] 'statement_type'
    [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

The second parameter “order” can take three values:

  1. First: The Trigger is fired First
  2. Last: The Trigger is fired Last
  3. None: The Trigger is fired in random order

The third parameter “statement_type” specifies the SQL statement that fires the Trigger. statement_type is varchar(50) and can be INSERT, UPDATE, DELETE, LOGON, or any Transact-SQL statement event listed in DDL Events. Event groups cannot be specified.

There can be only one First and one Last trigger for each statement on a single table.

If a First trigger is already defined on the table, database, or server, you cannot designate a new trigger as First for the same table, database, or server for the same statement_type. This restriction also applies Last triggers.

Permissions Needed to set the Trigger Order:

  1. To set the order of a DDL trigger with server scope (created ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission.
  2. To set the order of a DDL trigger with database scope (created ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission.
  3. To set the order of a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

I hope now it is clear to you and you will be able to set the execution order of Triggers if needed.

Continue Coding and Continue Learning !!

Thanks

Subhro Saha

Reference:MSDN

Advertisements