SQL Server 2008 中的唯一日期范围字段 [英] Unique date range fields in SQL Server 2008
问题描述
我有一个表,其中包含两个名为 StartTime 和 EndTime 的字段.两者都是 TIME 字段.
I have a table that consists of, among other things, two fields named StartTime and EndTime. Both are TIME fields.
我想添加一个约束,以防止插入与预先存在的时间范围重叠的任何记录.例如.如果 StartTime = 5:00, EndTime = 10:00 的记录已经存在,我希望 StartTime = 6:00, EndTime = 9:00 的插入由于重叠而失败.
I want to add a constraint preventing the insertion of any records that overlap with preexisting time ranges. E.g. if a record already exists with StartTime = 5:00, EndTime = 10:00, I would want an insert with StartTime = 6:00, EndTime = 9:00 to fail due to the overlap.
有没有办法做到这一点,不管有没有触发器?
Is there any way to accomplish this, with or without triggers?
推荐答案
下面的触发器应该可以工作 - 也可以使用检查约束来做到这一点,但是 这篇文章有点让我头疼.
The trigger below should work - it is also possible to do this with check contraints, but the logic shown in this post kind of hurts my head.
CREATE TRIGGER [dbo].[DateRangeTrigger]
ON [dbo].[TargetTable]
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (SELECT t.starttime, t.endtime FROM TargetTable t
Join inserted i
On (i.starttime > t.starttime AND i.starttime < t.endtime AND i.UniqueId <> t.UniqueId)
OR (i.endtime < t.endtime AND i.endtime > t.starttime AND i.UniqueId <> t.UniqueId)
OR (i.starttime < t.starttime AND i.endtime > t.endtime AND i.UniqueId <> t.UniqueId)
)
BEGIN
RAISERROR ('Inserted date was within invalid range', 16, 1)
IF (@@TRANCOUNT>0)
ROLLBACK
END
END
这篇关于SQL Server 2008 中的唯一日期范围字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!