If I have a table of events like this:
event_name begin_date(pk) end_date(pk)
------------------------------------------
holiday 2014-11-01 2014-11-05
holiday 2014-11-10 2014-11-12
big sale 2014-11-18 2014-11-25
monthly sale 2014-11-28 2014-11-30
How can I prevent inserting data if begin_date or end_date of the inserted data is in the period of any events?
Example:
This data won't be inserted:
holiday 2014-11-03 2014-11-08
this data will be inserted:
holiday 2014-11-06 2014-11-09
Can anyone help me solve this problem?
The best thing would be to avoid triggers and perform a check with if exists before inserting
IF NOT EXISTS (SELECT TOP 1 1 FROM MyTable WHERE @InsertedEndDate > begin_date AND @InsertedBeginDate < end_date)
BEGIN
--do actual insert/work
END
Its a simple check to find the first overlap. The Select TOP 1 1 is a trick to avoid actually fetching the data, it will return as soon as it matches a row that overlaps the date range you're actually trying to save
Triggers should be your last resort. If your application uses a stored procedure, it's better if you put the validation there. Or you could use a check constraint. This is the condition you need to use, from what I understand of your problem:
SELECT *
FROM Table
WHERE @begin_date BETWEEN begin_date AND end_date
OR @end_date BETWEEN begin_date AND end_date
OR @begin_date < begin_date AND @end_date > end_date
If that query returns any rows, those @begin_date and @end_date values should't be inserted.
I always think that if something can be constrained in the database, it should be. You never know which developer is going to disable a trigger, or bypass application code and run the insert directly, so while triggers and business logic is good, it is not fool proof.
The first thing I would do is constrain begin_date to be before end_date:
CREATE TABLE dbo.T
(
ID INT IDENTITY(1, 1) NOT NULL,
Event_name VARCHAR(50) NOT NULL,
begin_date DATE NOT NULL,
end_date DATE NOT NULL
);
ALTER TABLE dbo.T ADD CONSTRAINT CHK_T_ValidDates CHECK (Begin_date <= end_date);
Then (if you don't already have one) you can create a calendar table (which are incredibly useful anyway):
CREATE TABLE dbo.Calendar
(
Date DATE NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX UQ_Calendar_Date ON dbo.Calendar (Date);
GO
INSERT dbo.Calendar (Date)
SELECT TOP (7305) DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, '20000101')
FROM sys.all_objects a, sys.all_objects;
GO
Finally you can create an indexed view, to ensure that no dates are duplicated in your table:
CREATE VIEW dbo.TCheck
WITH SCHEMABINDING
AS
SELECT c.Date
FROM dbo.T
INNER JOIN dbo.Calendar AS c
ON c.Date >= t.begin_date
AND c.Date <= t.end_date;
GO
CREATE UNIQUE CLUSTERED INDEX UQ_TCheck_ID ON dbo.TCheck (Date);
In the tests I ran (comparing to a trigger) the indexed view performed about 50% better than the trigger, but neither performed well. Unfortunately, sometimes data integrity has a cost.
