防止使用 SQL 触发器插入重叠的日期范围
我有一个简化的表，如下所示：
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 的一部分。因此，插入表中的新记录始终与测试表中的自身连接。触发器总是会恢复转换。
我无法区分新记录和现有记录。因此，如果我排除相同的日期范围，我将能够在表中插入多个完全相同的范围。
主要问题是
是否可以编写一个按预期工作的触发器，而无需向我的测试表添加额外的标识列，我可以使用该触发器从我的 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
重要：如果没有身份就不可能是唯一的答案，我欢迎您提出它并给出合理的解释。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
进行两个小的更改，一切都会正常工作。
首先，向触发器添加一个 where 子句，以从连接中排除重复记录。然后，您将不会将插入的记录与其自身进行比较：
除此之外，这将允许精确的重复范围，因此您必须在两列之间添加唯一约束。实际上，您可能希望对每一列有一个唯一的约束，因为在同一天开始（或结束）的任何两个范围默认情况下都是重叠的。
Two minor changes and everything should work just fine.
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:
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.
我知道这个问题已经得到了解答，但我最近解决了这个问题，并提出了一些可行的方法（并且对每个插入的行进行单例查找时表现良好）。请参阅本文中的示例：
http://michaeljswart.com /2011/06/enforcing-business-rules-vs-avoiding-triggers-which-is-better/
（并且它不使用身份列）
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)