CODEWITHSUNDEEP
sql-servert-sqlvalidationtriggers
kastermester
kastermester

Reputation: 3064

Ensuring correct data in MS SQL Server, DB Level

Say I have a table with the following layout:

Id  Int PRIMARY KEY IDENTITY
DateFrom datetime NOT NULL
DateTo datetime NOT NULL
UserId Int

UserId is a foreign key to a users table, and well obviously there's more data which is irrelevant to this question.

What I want to do, is to make sure that per User, there can not be any overlapping "periods", that is, say we have a row of data for User 1, from the 15th of May 2009 to the 18th of May 2009, then there cannot be any other rows in the database that contains any of those dates (15-18th of May 2009, both dates included).

How would I go about this in the best way? My initial thinking would be to use triggers, but I'm not exactly sure how and/or if there's a better way of doing some more "custom" data validation like this?

Upvotes: 2

Views: 514

Answers (4)

Mostafa Elhemali
Mostafa Elhemali

Reputation:

One way to do it, especially if your granularity is at the day level (and by the way, if that is the case and you're SQL Server 2008, then you should probably use the date type instead of datetime), is to have another table like: UserId int, Day datetime, Primary Key (UserId, Day) And then insert, delete, update all the days for a user on any insert, delete, update in your original table and just let the server's uniqueness constraint on the PK do the validation for you.

Upvotes: 0

A-K
A-K

Reputation: 17080

A common problem. You can use constraints:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171391

You need to use an INSERT/UPDATE trigger. Make sure when you write the trigger that it handles the case where more than one row is being inserted or updated at a time.

Upvotes: 1

John Sansom
John Sansom

Reputation: 41819

My friend I beleive you are spot on. Triggers are designed for this exact reason, to implement Business Rules.

The Books Online documentation has a few examples that will help to get you started.

http://msdn.microsoft.com/en-us/library/ms189799.aspx

Upvotes: 2

Related Questions