Prevent inserting overlapping date ranges using a SQL trigger

Question

I have a table that simplified 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 inserting values that overlap an existing date range. I've written 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 it doesn't work, since my newly inserted record is part of both tables Test and inserted while inside a trigger. So the new record in inserted table is always joined to itself in the Test table. Trigger will always revert transation.

I can't distinguish new records from existing ones. So if I'd exclude same date ranges I would be able to insert multiple exactly-same ranges in the table.

The main question is

Is it possible to write a trigger that would work as expected without adding an additional identity column to my Test table that I could use to exclude newly inserted records from my exists() statement like:

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

Important: If impossible without identity is the only answer, I welcome you to present it along with a reasonable explanation why.

Answer 1

I know this is already answered, but I tackled this problem recently and came up with something that works (and performs well doing a singleton seek for each inserted row). See the example in this article: http://michaeljswart.com/2011/06/enforcing-business-rules-vs-avoiding-triggers-which-is-better/

(and it doesn't make use of an identity column)