使用SQL触发器防止插入重叠的日期范围

Question

我有一个简化后的表格，看起来像这样：

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

我希望编写一个触发器，防止插入与现有日期范围重叠的值。我已经编写了一个类似以下代码的触发器：

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

但是它不起作用，因为我的新插入的记录是触发器中两个表Test和inserted的一部分。所以插入表中的新记录总是与Test表中的自身连接。触发器将始终回滚事务。

我无法区分新记录和现有记录。因此，如果我排除相同的日期范围，我将能够在表中插入多个完全相同的范围。

主要问题是：

是否可能编写一个触发器，而不向Test表添加附加的标识列，以便我可以使用它来从我的exists（）语句中排除新插入的记录，例如：

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

重要提示：如果“无法在身份不明的情况下完成”是唯一答案，欢迎您提出，并附上合理的解释。

- Robert Koritnik

Answer 1

只需要进行两个小改变，一切都应该正常工作。

首先，在触发器中添加一个where子句来排除重复记录的连接。然后你就不会将插入的记录与自身进行比较：

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)

但是，这样会允许完全重复的范围，所以您需要在两列之间添加唯一约束。实际上，您可能希望在每个列上都有一个唯一约束，因为任何在同一天开始（或结束）的两个范围默认情况下是重叠的。

- Bill

---

谢谢。一个带有where子句的unique约束实际上可以使其工作。当您插入重叠的日期范围时，会出现错误。 - Robert Koritnik

Answer 2

我知道这个问题已经有答案了，但是我最近解决了这个问题，并想到了一个有效的方法（对于每个插入的行进行单例查找并执行良好）。请参考这篇文章中的示例： http://michaeljswart.com/2011/06/enforcing-business-rules-vs-avoiding-triggers-which-is-better/ （而且它不使用标识列）

- Michael J Swart

---

被踩了.. 触发器并不是问题，因为它们强制执行数据完整性。代码中的业务规则只有在开发人员记得应用它们时才起作用。我见过很多因此而产生的错误。如果你不能创建无效数据，就不需要检查或修复它。 - Quango

@Quango 我怀疑你只看了文章的标题，然后猜测了内容。请阅读帖子，我相信你会发现我们的观点是一致的。这篇帖子举了一个直接回答问题的触发器的例子。 - Michael J Swart