Data Management optimize

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 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!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

7 comments
monte_strickland
monte_strickland

What about the performance differences between constraints and triggers used for referential integrity?

slksport
slksport

Why do the count statements have: SaleDate >= @Today AND SaleDate > @Today Isn't that redundant? ">=" is a superset of ">" unless there's something I just don't get about what is happening (always possible!).

Tony Hopkinson
Tony Hopkinson

Constraints limit what data can be entered, triggers fire in the process of entering data. The only time you should use a trigger to block entry is when the schema for one reason or another means you have no choice. IE the contraint is loose, partially defined or would require an unacceptable level of rework of the existing schema. Do it simply because you can, leaves you with an inflatable dartboard. Any requirement to implement a constraint as a trigger is a big red flag in my book.

stephen.burns
stephen.burns

For example, I can use a trigger to ensure that any changes to a table are reflected in an Audit log. This can be very usefull in situations where a user/programmer/etc might bypass the application and update a table in the database directly. The trigger can capture who made the update and when, even if the application does not. This makes for a much more secure database. Trggers can also implement business logic directly into the database so for example, a reorder record can be created for a stock item when a threshold is reached.

chapman.tim
chapman.tim

Sorry about that, it is a typo on my part. It should be: SaleDate >= @Today AND SaleDAte < @Today + 1

jcelko212
jcelko212

This article forgot to mention that constraints can be used by the optimizer, while TRIGGERs cannot. Constraints are also portable, while TRIGGER code is not very portable. Another old trick to avoid triggers for constraints is to give access to the base table only thru a VIEW that has a WITH CHECK OPTION. This leads to declarative code that the optimizer can use. >> 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.

chapman.tim
chapman.tim

You can definetly design triggers to cascade updates and deletes, but you may have to worry about your triggers nesting, which the level is 32. You can accomplish the same thing (usually) with cascading referential integrity constraints.