SQL Server Triggers Tips & Tricks: The activation order

Triggers activation order

Introduction

Triggers are often one of the main reason of quarrel between Developers and DBAs, between those who customize a database application and those who develops it. They are often the first database objects to be blamed when the performance degrades. They seem easy to write, but writing efficient Triggers is not easy. They have many side effects, but also a very important characteristic: they allow solving problems that cannot be managed in any other application layer.

A Trigger is a special type of stored procedure: it is not called directly, but it is activated on a certain event with special rights that allow you to access in-coming and out-coming data that are stored in special virtual tables called `Inserted` and `Deleted`. Triggers exist in SQL Server since the version 1.0, even before CHECK constraint. They always work in the same unit-of-work of the T-SQL statement that has called them. There are different types of Triggers: Logon Trigger, DDL Trigger and DML Trigger; the most known and used type is Data Manipulation Language Trigger, also known as DML Trigger. This series of posts treats only aspects related to DML Triggers.

There are many options that modify run time Triggers’ behavior, they are:

1. Nested Triggers
2. Disallowed results from Triggers
3. Server Trigger recursion
4. Recursive Triggers

Each of these options has, of course, a default value in respect to the best practices of Triggers development. The first three options are Server Level Options and you can change their default value using sp_configure system stored procedure, whereas the value of the last one can be set at the Database Level.

What do you think about Triggers? In your opinion, based on your experience, are they useful or damaging? You will meet people who say: “Triggers are absolutely useful” and other people who say the opposite. Who is right? Reading the two bullet lists you will find the main reasons of the two different theory about Triggers.

People say that Triggers are useful because with them:

• You can develop customize business logics without changing the user front-end or the Application code
• You can develop an Auditing or Logging mechanism that could not be managed so efficiently in any other application layer

People say that Triggers are damaging because:

• They are sneaky!
• They can execute a very complex pieces of code silently!
• They can degrade performance very much
• Issues in Triggers are difficult to diagnose

As usual the truth is in the middle. I think that Triggers are a very useful tool that you could use when there are no other ways to implement a database solution as efficiently as a Trigger can do, but the author has to test them very well before the deployment on the production environment.

Triggers activation order

SQL Server has no limitation about the number of Triggers that you can define on a table, but you cannot create more than 2.147.483.647 objects per database; so that the total of Table, View, Stored Procedure, User-Defined Function, Trigger, Rule, Default and Constraint must be lower than, or equal to this number (that is the maximum number that will be represented by the integer data type).

Now, supposing that we have a table with multiple Triggers, all of them ready to fire on the same statement type, for example on the INSERT statement: “Have you ever asked yourself which is the exact activation order for those Triggers?” In other worlds, is it possible to guarantee a particular activation order?

The Production.Product table in the AdventureWorks database has no Triggers by design. Let’s create, now, three DML Triggers on this table, all of them active for the same statement type: the INSERT statement. The goal of these Triggers is printing an output message that allows us to observe the exact activation order. The following piece of T-SQL code creates three sample DML AFTER INSERT Triggers on Production.Product table.

USE [AdventureWorks];
GO
-- Create Triggers on Production.Product
CREATE TRIGGER Production.TR_Product_INS_1 ON Production.Product AFTER INSERT
AS
 PRINT 'Message from TR_Product_INS_1';
GO
CREATE TRIGGER Production.TR_Product_INS_2 ON Production.Product AFTER INSERT
AS
 PRINT 'Message from TR_Product_INS_2';
GO
CREATE TRIGGER Production.TR_Product_INS_3 ON Production.Product AFTER INSERT
AS
 PRINT 'Message from TR_Product_INS_3';
GO

Let’s see all Triggers defined on Production.Product table, to achieve this task we will use the sp_helptrigger system stored procedure as shown in the following piece of T-SQL code.

USE [AdventureWorks];
GO
EXEC sp_helptrigger 'Production.Product';
GO

The output is shown in the following picture.

Picture 1 — All Triggers defined on Production.Product table

Now the question is: Which will be the activation order for these three Triggers? We can answer to this question executing the following INSERT statement on Production.Product table, when we execute it, all the DML INSERT Triggers fire.

USE [AdventureWorks];
GO
INSERT INTO Production.Product
(
 Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
 ReorderPoint, StandardCost, ListPrice, DaysToManufacture,
 SellStartDate, RowGUID, ModifiedDate
)
VALUES
(
 N'CityBike', N'CB-5381', 0, 0, 1000, 750, 0.0000, 0.0000, 0,
 GETDATE(), NEWID(), GETDATE()
);
GO

The output returned shows the default Triggers activation order.

Message from TR_Product_INS_1
Message from TR_Product_INS_2
Message from TR_Product_INS_3

As you can see in this example, Triggers activation order coincides with the creation order, but by design, Triggers activation order is undefined.

If you want to guarantee a particular activation order you have to use the sp_settriggerorder system stored procedure that allows you to set the activation of the first and of the last Trigger. This configuration can be applied to the Triggers of each statement (INSERT/UPDATE/DELETE). The following piece of code uses sp_settriggerorder system stored procedure to set the Production.TR_Product_INS_3 Trigger as the first one to fire when an INSERT statement is executed on Production.Product table.

USE [AdventureWorks];
GO
EXEC sp_settriggerorder
 @triggername = 'Production.TR_Product_INS_3'
 ,@order = 'First'
 ,@stmttype = 'INSERT';
GO

At the same way, you can set the last Trigger fire.

USE [AdventureWorks];
GO
EXEC sp_settriggerorder
  @triggername = 'Production.TR_Product_INS_2'
  ,@order = 'Last'
  ,@stmttype = 'INSERT';
GO

Let’s see the new Triggers activation order by executing another INSERT statement on Production.Product table.

USE [AdventureWorks];
GO
INSERT INTO Production.Product
(
  Name, ProductNumber, MakeFlag, FinishedGoodsFlag,
  SafetyStockLevel, ReorderPoint, StandardCost, ListPrice,
  DaysToManufacture, SellStartDate, RowGUID, ModifiedDate
)
VALUES
(
  N'CityBike Pro', N'CB-5382', 0, 0, 1000, 750, 0.0000, 0.0000, 0,
  GETDATE(), NEWID(), GETDATE()
);
GO

The returned output shows our customized Triggers activation order.

Message from TR_Product_INS_3
Message from TR_Product_INS_1
Message from TR_Product_INS_2

In this first part you have learnt how to set the activation of the first and of the last Trigger in a multiple DML AFTER INSERT Triggers scenario. Probably, one question has come to your mind: “May I set only the first and the last Trigger?” The answer is: “Yes, currently you have the possibility to set only the first Trigger and only the last Trigger for each statement on a single table”; as a friend of mine says (he is a DBA): “You can set the activation only of the first and of the last Trigger because you should have three Triggers maximum for each statement on a single table! The sp_settriggerorder system stored procedure allows you to set the first and the last Trigger fires, so that the third one will be in the middle, between the first and the last”.

All examples in this post are based on AdventureWorks database that you can download from codeplex website at this link.

Enjoy!