To aviod date overlapping in a table using check constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

To aviod date overlapping in a table using check constraint

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-23 : 10:10:06

Sriman writes "Hi,

I have a table with the structure

create table rooms(Id numeric(3),St_dt datetime, end_dt datetime, company_id numeric(4), no_of_room numeric(4))

(it's regarding to the room allocation to a company in a hotel.)

1.st_dt is the room alloted start date and
2.end_dt is the room alloted end date and
3.company_id is comp_id is company id
4.no_of_rooms is number of rooms alloted to that company between the st_dt and end_dt

here the data must be like this:

Id St_dt end_dt hot_id no_of_room
1 01/01/2007 01/15/2007 1 5
2 01/16/2007 01/24/2007 1 7
3 01/25/2007 01/30/2007 1 5

the dates can' not over lap like this

1 01/01/2007 01/20/2007 1 5
2 01/15/2007 01/21/2007 1 7

ie the new inserted row's st_dt must not be in between the previous row's st_dt and end_dt

I know that we can use the check constraint in order to avoid any invalid data to insert into the table
but is it possible to check inserted data across remaining rows values in the table

means, can we chk the present inserting row's st_dt must be less than previous row's end_dt

if not possible, what r other possiblities to handle this case?

I think one alternative is, to create a trigger on insert and handle the error in case of date overlapping
is it possible?

pls suggest.

thanks in advance,
Sriman"

csundaresh
Starting Member

5 Posts

Posted - 2007-01-25 : 11:44:15

Why don't you use INSTEAD OF INSERT trigger on the rooms table. This way you can check the row being inserted for date over lap and if in error discard this row by deleting it from inserted.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-25 : 12:00:11

You should put the logic for this in the stored procedure that inserts the data.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources