Comparing SQL Server constraints and DML triggers

Over the years, I have witnessed many developers being confused about when to use data-manipulation language (DML) triggers vs. when to use constraints.

I have seen numerous occasions where both objects have been used incorrectly, which has led to problems. This post sheds some light on when to use constraints and when to use DML triggers, so that you will not have to deal with the correction efforts that I have had to experience.

What are constraints and DML triggers?

A constraint is an object the database engine uses to constrain data in one table or a relationship of tables in order to maintain database integrity. These constraints include CHECK, UNIQUE, PRIMARY KEY, etc. Here are more details about how to define constraints in TSQL.

An AFTER trigger is a special type of TSQL code block that executes when a DML statement is executed against the table the trigger is defined on. (I refer only to this type of trigger for the purpose of this article.)

When to use constraints vs. DML triggers

It is more advantageous to use a constraint than a trigger (if you can use a constraint in your situation). You can always write a trigger to do the same work that a type of constraint can do, but it typically doesn't make sense to do so.

Consider the use of a foreign key constraint vs. a DML trigger. The purpose of a foreign key constraint is to ensure that the values allowed into one or more columns in one table are present in one or more columns in a separate table. You can create the same functionality using a DML trigger. The code below creates the SalesHistory and l_SalesProducts tables that I will use in my examples and loads each with some data.

IF OBJECT_ID(\'SalesHistory\')>0     
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(           
       [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,           
       [Product] [varchar](10) NULL,                 
       [SaleDate] [datetime] NULL,                 
       [SalePrice] [money] NULL
)
GO DECLARE @i SMALLINT
SET @i = 1WHILE (@i <=100)
 BEGIN                       
       INSERT INTO SalesHistory(Product, SaleDate, SalePrice)                       
       VALUES(\'Computer\', DATEADD(mm, @i, \'3/11/1919\'), DATEPART(ms, GETDATE()) + (@i + 57))     
       INSERT INTO SalesHistory(Product, SaleDate, SalePrice)           
       VALUES(\'BigScreen\', DATEADD(mm, @i, \'3/11/1927\'), DATEPART(ms, GETDATE()) + (@i + 13))                 
       INSERT INTO SalesHistory(Product, SaleDate, SalePrice)               
       VALUES(\'PoolTable\', DATEADD(mm, @i, \'3/11/1908\'), DATEPART(ms, GETDATE()) + (@i + 29))                        SET @i = @i + 1
END
GOINSERT INTO l_SalesProducts(Product)
SELECT \'BigScreen\'
UNION
SELECT \'Computer\'
UNION
SELECT \'PoolTable\'

Now that I have a couple of tables and some data to work with, let's create a foreign key constraint on the SalesHistory table.

ALTER TABLE SalesHistory ADD CONSTRAINT fk_SalesHistory FOREIGN KEY (Product) REFERENCES l_SalesProducts(Product)

This constraint ensures that when a record is inserted into the SalesHistory table, the value inserted into the Product field must also be present in the l_SalesProducts table.

You can simulate this same functionality with a DML trigger. The code below creates a trigger on the SalesHistory table that checks if the values inserted or updated in the SalesHistory table make the product value in the l_SalesProducts table.

CREATE TRIGGER tr_SalesHistory on SalesHistory
FOR INSERT, UPDATE
AS
 BEGIN
       IF UPDATE(Product) AND
       @@ROWCOUNT <>
(
SELECT COUNT(*)
              FROM INSERTED i
              JOIN l_SalesProducts s ON i.Product = s.Product)
       BEGIN
              ROLLBACK TRANSACTION
              SELECT \'Different Results, an error has occurred.\'
              --//THROW CUSTOM ERROR MESSAGE
       END
END

There is a lot more work involved in creating your solution in this fashion. Work time is not the only thing that will suffer for this solution. Because constraints are built in SQL Server functionality, they tend to be really good at what they are aimed to do and will generally perform a lot better than the same functionality performed in a trigger. When a DML operation is performed, an INSTEAD OF trigger will be fired first, then our foreign key constraint check, followed by the AFTER trigger. This means that any type of foreign key constraints defined upon the table must be satisfied before the AFTER trigger is invoked.

Now let's look at a scenario where you want to use a trigger instead of a constraint. Triggers are great in situations where checks need to be made that compare current values in a table with what is currently being entered into the table so that certain thresholds are not met. Consider the following business situation.

MyCompany.com recently implemented a business rule that the number of BigScreen products sold in a day can never exceed five if the product is sold for less than $500. The move is an effort to keep discount prices from exceeding the company's wishes. We will look at one of the several ways in which this can be accomplished.

First, the user interface can query the database to know if the product being entered exceeds the threshold. This approach works, but it requires additional business logic in the user interface, which is something the company may oppose. It would be very difficult to implement a constraint in this scenario, but to do so, some additional programming logic would still need to occur. A reasonable solution to this problem is to use a trigger because triggers are good at comparing past and current values and making decisions based upon those values. The code below creates the trigger that accomplishes our objective.

CREATE TRIGGER tr_MaxProductSales ON SalesHistory
FOR INSERT, UPDATE
AS
 BEGIN
       DECLARE @Product CHAR(150)
       DECLARE @Today SMALLDATETIME
       DECLARE @InsertedCount INT, @CurrentCount INT
       DECLARE @MaxRecordCount TINYINT
       DECLARE @MinSalePrice MONEY        IF @@ROWCOUNT > 0
       BEGIN
              SET NOCOUNT ON
SET @Today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)                        SET @Product = \'BigScreen\'
              SET @MaxRecordCount = 5
              SET @MinSalePrice = 500              SELECT @InsertedCount = COUNT(*)
              FROM INSERTED
              WHERE
                     Product = @Product AND
                     SalePrice < @MinSalePrice AND
                     SaleDate >= @Today AND
                     SaleDate < @Today + 1             SELECT @CurrentCount = COUNT(*)
              FROM SalesHistory
              WHERE
                     Product = @Product AND
                     SalePrice < @MinSalePrice AND
                     SaleDate >= @Today AND
                     SaleDate < @Today + 1            IF @CurrentCount + @InsertedCount > @MaxRecordCount
              BEGIN
                     PRINT \'Too many product sales for today.\'
                     ROLLBACK TRANSACTION
              END
       END
END

In this trigger, I am checking to see if the number of BigScreen products updated to or inserted into the table coupled with the number of BigScreen products sold for the current date, which are under $500, exceeds five products. If that value is exceeded, the trigger rolls the transaction back, and the products are not added.

Conclusion

Constraints and DML triggers both have certain types of things they do well. Constraints are great at maintaining database integrity for database fields and relationships between database tables. Triggers are great for checking past vs. current values and making decisions based on that data. In my experience, it is usually much more favourable to use constraints when possible and leave the trickier business and auditing logic for use in triggers.