防止使用SQL触发器插入重叠的日期范围 [英] Prevent inserting overlapping date ranges using a SQL trigger

问题描述

我有一个简化的表，如下所示：

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

但这没用，因为我新插入的记录是两者的一部分触发器中的 Test 和 insert 表。因此，插入表中的新记录总是在Test表中与其自身联接。触发器将始终还原翻译。

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.

是否可以编写一个可以按预期工作的触发器，而无需在我的测试表中添加其他标识列，我可以使用该列从我的 exists（）中排除新插入的记录语句，例如：

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.

推荐答案

两个小改动

首先，在触发器中添加where子句，以将重复的记录从联接中排除。这样您就不会将插入的记录与其自身进行比较：

First, add a where clause to your trigger to exclude the duplicate records from the join. Then you won't be comparing the inserted records to themselves:

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)

除了，这将允许精确的重复范围，因此您必须在两列之间添加唯一约束。实际上，您可能希望在每一列上使用唯一的约束，因为默认情况下，同一天开始（或结束）的任何两个范围都是重叠的。

Except, this would allow for exact duplicate ranges, so you will have to add a unique constraint across the two columns. Actually, you may want a unique constraint on each column, since any two ranges that start (or finish) on the same day are by default overlapping.

这篇关于防止使用SQL触发器插入重叠的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！