Enforcing Business Rules Vs. Avoiding Triggers: Which Is Better?

June 15, 2011

Enforcing Business Rules Vs. Avoiding Triggers: Which Is Better?

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: "sql server", business rules, triggers — Michael J. Swart @ 12:00 pm

Takeaway: I like to develop database stuff using rules of thumb. When two rules of thumb are at odds, what should I do? This post, I explore the advice: It depends with an example.

I first give the two rules of thumb:

Rule Of Thumb #1: Avoid Triggers

I don’t like triggers too much and I avoid them whenever possible. It’s just that they

hide so much that might go wrong,
They’re a huge source of frustration when debugging problem queries
They’re often a symptom of sloppy db design and
it’s a common mistake to write them using the assumption that the inserted virtual table contains a single row

Rule Of Thumb #2: Enforce Business Rules In The Database

It’s good to enforce business rules at the database:

Databases often outlive the applications that are built against it
It’s too difficult to enforce the rules in a single place only (e.g. the application). The effort needed to keep the data clean becomes unsustainable.
They can help development. It shouldn’t need saying that if foreign keys throw errors during development, don’t get rid of them! They’re doing their job!

The Example: No Overlapping Intervals

This is where the two rules of thumb collide! It’s because not all business rules are easy to implement. Take this example, the case of preventing overlapping intervals. It’s a common scenario. Some examples are seen

when scheduling resources (planes at a gate, weddings at a hall, meetings in rooms)
when enforcing one thing at a time (terms of a particular office, factory equipment status)

To restrict overlapping intervals, the usual business rule enforcement tricks don’t work:

They cannot be enforced easily with database table design (~~foreign keys don’t apply because the constraint applies to a single table~~ See Comments).
They can’t be enforced easily with checks because a check constraint only enforces rules on values in a single record. But the overlapping interval restriction is a restriction based on two records.
And unique indexes can’t help either.

But the constraint we want to enforce could be implemented decently with a trigger. Consider this table that tracks what I call shifts:

USE tempdb
GO
 
CREATE TABLE SHIFTS (
	shiftId int identity primary key,
	beginTime datetime2 not null,
	endTime datetime2 not null,
	check (beginTime < endTime )
)
CREATE UNIQUE INDEX IX_SHIFTS 
	ON SHIFTS(beginTime)
	INCLUDE(endTime)
GO

We’ve already enforced that shifts go forward in time with a check and that no two shifts will start at the same time with a unique index. Here’s a trigger which will enforce that no two shifts overlap:

CREATE TRIGGER t_NoOverlappingShifts 
   ON  SHIFTS
   AFTER UPDATE, INSERT
AS 
 
IF EXISTS (
	--next shift starts after this one ends
	SELECT 1
	FROM inserted
	CROSS APPLY (
		SELECT TOP 1 beginTime
		FROM SHIFTS 
		WHERE beginTime > inserted.beginTime
		ORDER BY beginTime ASC
	) AS NextShift(beginTime)
	WHERE NextShift.beginTime < inserted.endTime
)
BEGIN
	RAISERROR ('Error: Shifts cannot overlap.', 16, 1)
	ROLLBACK TRANSACTION
END
 
IF EXISTS (
	--previous shift ends before this one starts
	SELECT 1
	FROM inserted
	CROSS APPLY (
		SELECT TOP 1 endTime
		FROM SHIFTS 
		WHERE beginTime < inserted.beginTime
		ORDER BY beginTime DESC
	) AS PrevShift(endTime)
	WHERE PrevShift.endTime > inserted.beginTime
)
BEGIN
	RAISERROR ('Error: Shifts cannot overlap.', 16, 2)
	ROLLBACK TRANSACTION
END
GO

The trigger performs decently, and it does its job, but it’s not suited for every situation (e.g. bulk load operations)

My Opinion

So I ask myself the question: Is it good to create the trigger in this case?. I’m going to say that this trigger is good and that the value of enforcing business rules here is better than avoiding triggers because

the trigger doesn’t have any “side effects”. What I mean is that besides sometimes failing, the data in the database won’t be different than if the trigger didn’t exist
the trigger handles inserts or updates of multiple rows correctly
this trigger is not a bandaid for sloppy database design (like those maintenance triggers that keep redundant data in synch)

But I’d like to hear what you think in the comments.

P.S. It occurs to me that maybe you don’t even have the same rules of thumb that I mentioned. So what do you think? triggers: good or bad?

-- Comments (29)

29 Comments »

In this case, it might be worth it to look at implementing a business rules engine such as Blaze Advisor. Another way would be to enforce your business rules as part of the ETL process as described in my blog post here:
http://bidn.com/blogs/MMilligan/bidn-blog/1882/ssisy-spacek-and-fico-blaze-advisor-bre-and-etl

(You could do this even without using a BRE if you were willing to roll your own through business rules configuration tables that your ETL process could use.)

Comment by Mike Milligan — June 15, 2011 @ 1:32 pm
Hi Mike,
Thanks for stopping by. I have to admit that I had to google a couple of the terms you used here and look at your link. I knew that ETL means Extract Tranform and Load and ETLs imply a Data Warehouse project. But BRE (Business Rules Engine) was new to me. As I understand it, BRE’s are only used with an ETL process.

I usually work more with OLTP databases. And it’s the (unstated) point of view I was using when I wrote this article.

But your article makes me wonder (probably a rookie question) where do business rules get enforced (if anywhere) in a Data Warehouse? The link you included looks like an attempt to answer that question. Am I right?

Comment by Michael J. Swart — June 15, 2011 @ 2:14 pm
Actually, the FICO Blaze Advisor software seemed to me to be written specifically for an OLTP database implementation for business rules. My opinion is that it is geared towards more of an application centric tool rather than for loading a data warehouse.

When loading a data warehouse, typically the business rules are hard-coded either in the SQL queries or stored procedures that serve as the source data, or in the ETL tool (Microsoft SSIS for example.)

I think it is a good idea to segregate the business rules from the SQL statements and the ETL process by creating configuration tables that the SQL or ETL process accesses. Blaze Advisor can make this process a little easier because it has a rich feature set for the creation and modification of business rules. Ideally by the business users themselves. That being said, I don’t think a product like Blaze Advisor is absolutely necessary to meet the goal of segregating these rules from the process.

A quick example might make this point a little more clear from an ETL perspective. Imagine you are loading a data warehouse fact table that will serve the purpose of analyzing refunds from a transaction based OLTP system. Your source table contains all transactions, not just refunds. Refunds are currently defined using a business code or ID called transactionTypeCode. Refund transactionTypeCodes include 0100,0250, and 0750 and are defined by a proprietary third party web store application. Typically a data mart for refunds would be loaded using a SQL statement such as this: SELECT COL1, COL2, COL3 FROM TransactionTable where TransactionTypeCode in (‘0100′,’0250′,’0750’);
If we were to segregate the business rule from the ETL process we could store valid refund transactiontypeCodes in a lookup table that the business users could modify using a web front end. The SQL statement would now read something like this: SELECT COL1, COL2, COL3 FROM TransactionTable where TransactionTypeCode in (Select TransactionTypeCode from BR_TransTypes where TransactionType = ‘Refund’);

If a new version of the web front end is released and they’ve changed all the codes that relate to refunds or added new ones you simply need to update the contents of the BR_TransTypes table instead of actually modifying any SQL or SSIS software code.

This is an extremely oversimplified example; but, it makes the point.

Take a look at FICO’s Blaze Advisor software. They offer an evaluation download and the tutorials are fairly easy to go through. I am fairly confident you could achieve the same results you are trying to achieve with your triggers by using the Blaze Advisor software.

Comment by Mike Milligan — June 15, 2011 @ 3:05 pm
Hi Mike,

I took a quick look at the Blaze Advisor software and I’m afraid I have to give it a pass. As far as I can tell the best it can do is to enforce business rules on data before it gets to the OLTP database. But I advocate enforcing business rules inside the database as well no matter where else they might be enforced.

BTW, when I mean business rules, I mean basic things like enforcing that a value is not null, that a course begins before it ends, and that foreign keys are enforced as in your TransactionType to BR_TransTypes example. Maybe it’s a difference in terminology that we use (OLTP guy vs BI guy) but SQL statements don’t enforce business rules, DB schema and DB constraints do.

Comment by Michael J. Swart — June 15, 2011 @ 4:44 pm
[…] Enforcing Business Rules Vs. Avoiding Triggers: Which Is Better? – The business logic debate continues, Michael J. Swart (Blog|Twitter) discusses. […]

Pingback by Something for the Weekend – SQL Server Links 17/06/11 — June 17, 2011 @ 6:49 am
Hi Michael,

Just found your articel and I think your approach here is spot on.

People should always remember what the word Database actually means – Data Base – its the Base for your Data – if things go wrong here then the rest of the app will fail. Unless you build a system that totally restricts any access to the database except through some data layer then it it impossible to prevent someone making changes directly to the data in the database – so, even though implementing business rules in the DB may not be much fun its actually a very important thing to do if you want to maintain the integrtiy of the entire system.

I hate triggers – they are evil – but until Msft implement a more elegant solution to the overlapping date range scenario then a trigger is the only option. THanks for sharing the code.

cheers
Chris

Comment by ChrisW — June 18, 2011 @ 6:42 am
Couldn’t you also implement the same by creating a table with a primary key constraint or an unique index on the Resource, Date, ShiftStart and ShiftEnd Keys to hold reservation data?

For example, your shift dimension has the following values:
ShiftKey HourId MinuteId
1 8 00
2 8 15
3 8 30

For a reservation from 8:00 – 8:30 you would need two rows added to your fact table:
ResourceKey DateKey ShiftStartKey ShiftEndKey ReservedByKey
867 20110620 1 2 5309
867 20110620 2 3 5309

If your primary key is on ResourceKey, DateKey, ShiftStartKey and ShiftEndKey attempting to schedule any particular resource on the same combinations of those keys would result in a primary key violation. If you need finer control than 15 minute incrementals turn them into 1 minute increments in your shift dimension.

Also, you’re totally right about Blaze… I don’t think it would implement the business rules at the database level. It is basically supposed to be a central repository for all business rules for all applications. When an application needs a decision, it sends the data to Blaze to evaluate.

Mike

Comment by Mike Milligan — June 18, 2011 @ 8:47 am
Hi Mike, For what it’s worth, here’s the test script I used for my trigger above:
Your solution would be fine if shifts were always a constant, short length and started and ended on a small set of times for a day. When shifts can be any length, and start and end at any time, the problem becomes trickier.

— begin time after end time?
INSERT SHIFTS (beginTime, endTime)
VALUES (‘2011-01-01’, ‘2010-01-01’)
/* violates the check constraint */

— matching begin times?
INSERT SHIFTS (beginTime, endTime)
VALUES (‘2011-01-01’, ‘2011-02-01’)
,(‘2011-01-01’, ‘2011-02-15’)
/* violates unique index */

— matching end times?
INSERT SHIFTS (beginTime, endTime)
VALUES (‘2011-01-15’, ‘2011-02-01’)
,(‘2011-01-01’, ‘2011-02-01’)
/* trigger prevents these values */

— insert concurrent shifts
INSERT SHIFTS (beginTime, endTime)
VALUES (‘2011-01-01’, ‘2011-02-01’)
,(‘2011-02-01’, ‘2011-03-01’)
/* works as expected */

— overlaps beginning of existing shift
INSERT SHIFTS (beginTime, endTime)
VALUES (‘2010-12-15’, ‘2011-01-15’)
/* prevented by trigger */

— overlaps end of existing shift
INSERT SHIFTS (beginTime, endTime)
VALUES (‘2011-02-15’, ‘2011-03-15’)
/* prevented by trigger */

— contained in an existing shift
INSERT SHIFTS (beginTime, endTime)
VALUES (‘2011-01-10’, ‘2011-01-20’)
/* prevented by trigger */

— contains an existing shift
INSERT SHIFTS (beginTime, endTime)
VALUES (‘2010-12-15’, ‘2011-03-15’)
/* prevented by trigger */

— update a set of rows which don’t overlap *after* update
UPDATE SHIFTS
SET endTime = DATEADD(day, 15, endTime),
beginTime = DATEADD(day, 15, beginTime)
/* works well */

— update a set of rows which do overlap after update
UPDATE SHIFTS
SET endTime = DATEADD(day, 15, endTime)
/* prevented by trigger */

Comment by Michael J. Swart — June 18, 2011 @ 11:20 am
ChrisW and Mike M,
Thanks for your comments. I love these discussions and next week’s blog posts will be indirectly about you guys! (commenters in general vs. lurkers). Check back next Wednesday.

Comment by Michael J. Swart — June 18, 2011 @ 11:23 am
Mike M: And good catch on suggesting the ResourceKey, A shift table, or a reservation table almost always has a resource to be “reserved”.

Comment by Michael J. Swart — June 18, 2011 @ 11:25 am
Michael,

I completely agree with the message of your post. I liked it; it is always great to see someone really care about data integrity. However, I have a few concerns about the example you used to demonstrate your point.

1. Your triggers may not work under snapshot isolation – you can successfully insert two identical intervals from two connections, and neither of them will see uncommitted changes being done by another. Here is a good example: “Snapshot isolation: A threat for integrity? (Part 2) ”
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/26/Snapshot_and_integrity_part_2.aspx

2. I disagree with the following: “foreign keys don’t apply because the constraint applies to a single table”. We can have a FK refer to the same table – I use self-referencing FKs all the time.

3. More specifically, we can enforce non-overlapping intervals using only constraints, as described in the following article: “Contiguous Time Periods”,

http://www.simple-talk.com/sql/t-sql-programming/contiguous-time-periods/

Comment by AlexK — June 18, 2011 @ 1:50 pm
Hi Alex,
Great comments.
Re. 1: You’re absolutely right about snapshot isolation level. It’s a caveat that one should be aware of. And now that I think about it, I don’t think READ UNCOMMITTED is safe either. Imagine this scenario Transaction 1 deletes everything, Transaction 2 inserts rows that would overlap and commits, Transaction 1 rolls back.

Re. 2: You’re right of course, I’ve updated the article. I guess I dismissed foreign keys because they’re used to enforce equality relationships with other records. It never occurred to me to include a column like PreviousShiftEnd in the same row. (I don’t know what made me write that foreign keys can’t apply to a single table)

Re. 3: It’s an interesting way to tackle this problem. And it’s almost a silver bullet (enforcing business rules without triggers). I guess some of the drawbacks that I see are: (1) it includes values which properly belong to another record. (2) It works well for history tables, but for other uses where values are updated or deleted (like when modifying or cancelling reservations) it becomes trickier. (3) simple ad hoc queries are no longer supported. Like when deleting a single shift.

Comment by Michael J. Swart — June 19, 2011 @ 1:36 pm
Hello Michael! This post is a great follow-up to our discussion last week where I had mentioned using SQL as a possibility for encapsulating business rules in SQL functions. Data integrity business rules are even more important, and I think you have stated this quite well. As much as I despise triggers for the same reasons you provided as reasons to avoid (the hrs I have spent trying to find a bug in stored procs only to finally discover it’s a trigger’s fault – oy!), I agree that sometimes there are good reasons to use a trigger. Particularly important to me is your conclusion, that when using a trigger that “the data in the database won’t be different than if the trigger didn’t exist”. That is the key to avoiding debugging headaches IMO! Thank you for sharing.

-VG

Comment by VGrimes — June 20, 2011 @ 8:10 am
I know what you mean VGrimes,
I think I can sum up my experience with triggers as:
Debugging headaches caused > Coding headaches solved

Comment by Michael J. Swart — June 20, 2011 @ 8:58 am
Hi!
Interesting article, but why not using a function:

USE tempdb;
GO
IF OBJECT_ID(‘SHIFTS’,’U’) IS NOT NULL DROP TABLE SHIFTS;
IF OBJECT_ID(‘FN_No_Shift_Overlaps’,’FN’) IS NOT NULL DROP FUNCTION dbo.FN_No_Shift_Overlaps;
GO
CREATE TABLE SHIFTS (
shiftId int identity primary key,
beginTime datetime2 not null,
endTime datetime2 not null,
check (beginTime b.beginTime AND a.beginTime<b.endTime)
RETURN 0;
ELSE RETURN 1;
END
GO
ALTER TABLE shifts ADD CHECK (dbo.FN_No_Shift_Overlaps()=1);
GO
INSERT INTO SHIFTS
VALUES ( '2011-07-10 14:30:18' , '2011-07-20 14:30:18' ),
( '2011-07-25 14:30:18' , '2011-07-30 14:30:18' )–ok

INSERT INTO SHIFTS
VALUES ('2011-07-28 14:30:18' , '2011-08-02 14:30:18');–ko

Comment by john — July 15, 2011 @ 10:54 am

oops forgot function:

USE tempdb ;
GO
IF OBJECT_ID('SHIFTS' , 'U') IS NOT NULL 
   DROP TABLE SHIFTS ;
IF OBJECT_ID('FN_No_Shift_Overlaps' , 'FN') IS NOT NULL 
   DROP FUNCTION dbo.FN_No_Shift_Overlaps ;
GO

CREATE FUNCTION dbo.FN_No_Shift_Overlaps ( )
RETURNS BIT
AS 
    BEGIN
        IF EXISTS ( SELECT
                        *
                    FROM
                        SHIFTS a
                      , SHIFTS b
                    WHERE
                        a.beginTime > b.beginTime
                        AND a.beginTime < b.endTime ) 
           RETURN 0 ;
        ELSE 
           RETURN 1 ;
    END
GO

CREATE TABLE SHIFTS
       (
         shiftId INT IDENTITY
                     PRIMARY KEY
       , beginTime DATETIME2 NOT NULL
       , endTime DATETIME2 NOT NULL
       , CHECK ( beginTime  b.beginTime
                        AND a.beginTime < b.endTime ) 
           RETURN 0 ;
        ELSE 
           RETURN 1 ;
    END
GO

ALTER TABLE shifts ADD CHECK (dbo.FN_No_Shift_Overlaps()=1) ;
GO

INSERT  INTO SHIFTS
VALUES
        ( '2011-07-10 14:30:18' , '2011-07-20 14:30:18' ),
        ( '2011-07-25 14:30:18' , '2011-07-30 14:30:18' ) ; --ok
        
INSERT  INTO SHIFTS
VALUES
        ( '2011-07-28 14:30:18' , '2011-08-02 14:30:18' ) ;--ko

Comment by john — July 15, 2011 @ 11:04 am

John,

When we wrap UDFs in CHECK constraints, we expose ourselves to the following problems:

1. We can get false positives (invalid modifications pass through).

2. We can get false negatives (valid modifications fail).

3. The performance is terrible.

Google up this: “Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates”

Comment by Alex Kuznetsov — July 15, 2011 @ 11:42 am
Thanks for these comments

I think I need to reexamine some of my table definitions..

Comment by john — July 15, 2011 @ 12:04 pm
Thanks for your comments John and Alex.
I have to admit that I never considered functions when writing this article. I’m inclined to believe Alex based on his reputation alone. 🙂

Comment by Michael J. Swart — July 17, 2011 @ 10:15 pm
Related:
http://www.devx.com/dbzone/Article/31985/1954

Comment by Mike Milligan — July 18, 2011 @ 12:06 pm
You can use only one query inside trigger:
IF EXISTS (
SELECT 1
FROM inserted ins
Inner Join SHIFTS shi
On ins.beginTimeshi.beginTime
)

btw. I would use index on SHIFTS(beginTime,endTime) instead of included endTime.

Comment by Niikola — August 18, 2011 @ 10:03 am
Hi Niikola,
Thanks for stopping by!

The code in your post got garbled because my blog thought less-than and greater-than symbols were html. (Sorry about that) Do you mind reposting your trigger’s code wrapped with <pre> … </pre>? (Or you can email me if you like)

About the index. I’m also curious about why you would use an index on SHIFTS(beginTime,endTime). The beginTime is unique so putting the endtime in the index won’t help for seeks. And I can’t think of a scenario where using the SHIFTS(beginTime,endTime) would be better.

Comment by Michael J. Swart — August 18, 2011 @ 10:43 am
This is the trigger that I needed. Just as the example you mention, I am doing a clinic appointment system and it works perfectly. So yes, for an online appointment system, the trigger is a perfect solution.

Thanks

Comment by Javier Soques — July 17, 2013 @ 2:08 pm
Thanks for the feedback Javier! Glad I could help

Comment by Michael J. Swart — July 17, 2013 @ 2:13 pm
Great article Mike! I follow the same rules of thumb, but also feel the trigger is good in this case because preventing bad data is more important than avoiding the use of triggers. One thing I’d do a bit differently is use an INSTEAD OF INSERT, UPDATE trigger so that the identity key isn’t consumed.

Comment by Peter Pompeii — February 13, 2014 @ 11:07 am
Thanks Peter
“preventing bad data is more important than avoiding the use of triggers”
I think you summed up my opinion perfectly.

Comment by Michael J. Swart — February 13, 2014 @ 11:18 am
[…] ad-hoc mechanisms for enforcing different types of rules, including the use of: 1. Triggers: Enforcing Business Rules Vs. Avoiding Triggers: Which Is Better? | Michael J. Swart Enforcing Business Rules with Triggers 2. Constraints in a specially-designed data model (e.g. add […]

Pingback by Enforcing rules on generalised data model. - dBforums — June 9, 2014 @ 6:55 am
Mike,

I’d like to know if you would recommend a solution to the following scenario: I have three tables, each of them represents a different business entity and has different attributes. These three tables have an associative entity between them and a fourth table. Each record in this 4th table may only be associated with one or more records of a single table among the other three. Do you know if there is any way of enforcing this rule without the need of a trigger?

Comment by Samir Abrahao — March 1, 2016 @ 1:10 pm

Hi Samir,
I’m going to illustrate your question with an example from SQL Server’s system tables. If I misunderstood the problem let me know.

Think of sys.tables, sys.procedures and sys.views. They’re their own business entity which are collectively known as objects. In fact you can find them all in sys.objects.
Each objectid is unique. Say we wanted to create a fourth table called sys.object_collections. Those collections can only contain one type of object (tables, procedures or views)

create table sys.object_collections
(
    collectionName varchar(20),
    [type] varchar(max),
    primary key (collectionName, [type])
)
 
create table sys.object_collection_members
(
    collectionName varchar(20),
    [type] varchar(max),
    [object_id] bigint,
    foreign key (collectionName, [type])
        references sys.object_collections (collectionName, [type]),
    foreign key ([object_id], [type])
        references sys.objects ([object_id], [type])
)

This answer would satisfy the business rule (object collections of one type only enforced with no triggers). But there are some trade offs and extra work that you need to do. You have to evaluate if your situation is makes the trouble worth it.

Comment by Michael J. Swart — March 1, 2016 @ 4:46 pm

RSS feed for comments on this post. TrackBack URL

Michael J. Swart

June 15, 2011