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

本文介绍了防止使用SQL触发器插入重叠的日期范围的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.