Prevent Insertion of Overlapping Date Ranges with SQL Trigger

Question

I have a simplified table that looks like this:

create table Test ( ValidFrom date not null, ValidTo date not null, check (ValidTo > ValidFrom) ) 

I would like to write a trigger that prevents the insertion of values ​​that span the existing date range. I wrote a trigger that looks like this:

 create trigger Trigger_Test on Test for insert as begin if exists( select * from Test t join inserted i on ((i.ValidTo >= t.ValidFrom) and (i.ValidFrom <= t.ValidTo)) ) begin raiserror (N'Overlapping range.', 16, 1); rollback transaction; return end; end 

But this will not work, as my newly inserted record is part of both Test tables and inserted while inside the trigger. Thus, a new record in the inserted table is always connected to itself in the test table. A trigger will always return a translation.

I cannot distinguish new entries from existing ones. Therefore, if I excluded the same date ranges, I could insert several exactly the same ranges into the table.

The main question:

Is it possible to write a trigger that would work as expected without adding an additional identification column to my test table that I could use to exclude recently inserted records from my exists() statement, for example:

 create trigger Trigger_Test on Test for insert as begin if exists( select * from Test t join inserted i on ( i.ID <> t.ID and /* exclude myself out */ i.ValidTo >= t.ValidFrom and i.ValidFrom <=t.ValidTo ) ) begin raiserror (N'Overlapping range.', 16, 1); rollback transaction; return end; end 

It is important . If this is not possible without identification, this is the only answer, I welcome you to present it along with a reasonable explanation.

Answer 1

Two minor changes, and everything should work fine.

First add the where clause to your trigger to exclude duplicate entries from the connection. Then you will not compare the inserted records:

 select * from testdatetrigger t join inserted i on ((i.ValidTo >= t.ValidFrom) and (i.ValidFrom <= t.ValidTo)) Where not (i.ValidTo=t.Validto and i.ValidFrom=t.ValidFrom) 

In addition, this will allow you to use exact repeating ranges, so you have to add a unique constraint for the two columns. In fact, you may need a unique constraint for each column, since any two ranges that start (or end) on the same day overlap by default.

Answer 2

I know that this has already been answered, but I recently tackled this problem and came up with something that works (and does a good single search for each inserted row well). See an example in this article: http://michaeljswart.com/2011/06/enforcing-business-rules-vs-avoiding-triggers-which-is-better/

(and it does not use an identifier column)