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 more or 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. Listing A 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. Listing B 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. Listing C 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 favorable to use constraints when possible and leave the trickier business and auditing logic for use in triggers.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.


—————————————————————————————–

Get SQL tips in your inbox

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!