在 SQL Server 2008 中强制执行唯一的日期范围字段

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

首先，看看这个非常相似的问题:Unique date rangeSQL Server 2008 中的字段

上述问题的答案(来自 David Hall)是我需要的 90%，以下面的例子为例:

--#### 创建示例表创建表 [dbo].[tbl_Example]([ID] [int] IDENTITY(1,1) 非空，[股票代码] [varchar](20) 非空，[ValidFrom] [datetime] NOT NULL,[ValidUntil] [datetime] NOT NULL,[类型] [字符](1) 非空，约束 [PK_tbl_Example] 主键集群([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) 在 [主要]走--#### 添加触发器(基于 David Halls 的回答 - 只要 StockCode 或 Type 不同，我就允许重复的日期范围)设置 ANSI_NULLS ON走设置 QUOTED_IDENTIFIER ON走CREATE TRIGGER [dbo].[DateRangeTrigger] ON [dbo].[tbl_Example]插入，更新作为开始如果存在(选择 t.ValidFrom ，t.ValidUntilFROM tbl_Example tJOIN 插入 i ON ( i.StockCode = t.StockCodeAND i.Type = t.TypeAND i.ValidFrom >t.ValidFromAND i.ValidFrom t.ValidFromAND i.id <>t.id)或 ( i.StockCode = t.StockCodeAND i.Type = t.TypeAND i.ValidFrom t.ValidUntilAND i.id <>t.id) )开始RAISERROR ('日期范围不能与给定 StockCode 和类型的现有日期范围重叠', 16, 1)如果 (@@TRANCOUNT > 0)回滚结尾结尾走--#### 问题:它允许重复的日期范围，其中开始日期和结束日期 100% 相等INSERT [dbo].[tbl_Example] ([StockCode], [ValidFrom], [ValidUntil], [Type]) VALUES (N'Test', CAST(0x0000A13900000000 AS DateTime), CAST(0x0000A13B000000000' AS DateTime))INSERT [dbo].[tbl_Example] ([StockCode], [ValidFrom], [ValidUntil], [Type]) VALUES (N'Test', CAST(0x0000A13900000000 AS DateTime), CAST(0x0000A13B000000000' AS DateTime))

您会注意到，当 Dave 的触发器到位时，如果 [ValidFrom] 或 [ValidUntil] 是 相等，我仍然可以插入重叠日期强>.

向触发器添加更多 OR 子句以解释匹配的开始或匹配的结束或两者 - 调整触发器以防止出现最后一个子句的最简单方法是什么?>

澄清一下，我试图防止的重叠如下:

解决方案

我觉得这个条件更合适:

IF EXISTS ( SELECT * -- 不需要选择 EXISTS 中的列从 tbl_示例 t1内部联接tbl_示例 t2在t1.StockCode = t2.StockCode 和t1.Type = t2.Type 和t1.ValidFrom

它使用更简单的条件来检测重叠 - 如果两行在另一行结束之前开始，则存在重叠.

First up, check out this VERY similar question: Unique date range fields in SQL Server 2008

The answer on the above question (by David Hall) is 90% what i need with one small ommision, take the following example:

--#### Create example table
CREATE TABLE [dbo].[tbl_Example](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StockCode] [varchar](20) NOT NULL,
    [ValidFrom] [datetime] NOT NULL,
    [ValidUntil] [datetime] NOT NULL,
    [Type] [char](1) NOT NULL,
 CONSTRAINT [PK_tbl_Example] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--#### Add the trigger (based on David Halls answer - I allow duplicate date ranges as long as the StockCode or Type differ)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[DateRangeTrigger] ON [dbo].[tbl_Example]
    FOR INSERT, UPDATE
AS
    BEGIN
        IF EXISTS ( SELECT  t.ValidFrom ,
                            t.ValidUntil
                    FROM    tbl_Example t
                            JOIN inserted i ON ( i.StockCode = t.StockCode
                                                 AND i.Type = t.Type
                                                 AND i.ValidFrom > t.ValidFrom
                                                 AND i.ValidFrom < t.ValidUntil
                                                 AND i.id <> t.id
                                               )
                                               OR ( i.StockCode = t.StockCode
                                                    AND i.Type = t.Type
                                                    AND i.ValidUntil < t.ValidUntil
                                                    AND i.ValidUntil > t.ValidFrom
                                                    AND i.id <> t.id
                                                  )
                                               OR ( i.StockCode = t.StockCode
                                                    AND i.Type = t.Type
                                                    AND i.ValidFrom < t.ValidFrom
                                                    AND i.ValidUntil > t.ValidUntil
                                                    AND i.id <> t.id
                                                  ) ) 
            BEGIN
                RAISERROR ('Date range cant overlap existing date ranges for given StockCode and Type', 16, 1)
                IF ( @@TRANCOUNT > 0 ) 
                    ROLLBACK
            END
    END
GO

--#### Problem: its allowing duplicate date ranges where Start and End Dates are 100% equal
INSERT [dbo].[tbl_Example] ([StockCode], [ValidFrom], [ValidUntil], [Type]) VALUES (N'Test', CAST(0x0000A13900000000 AS DateTime), CAST(0x0000A13B00000000 AS DateTime), N'O')
INSERT [dbo].[tbl_Example] ([StockCode], [ValidFrom], [ValidUntil], [Type]) VALUES (N'Test', CAST(0x0000A13900000000 AS DateTime), CAST(0x0000A13B00000000 AS DateTime), N'O')

You'll note that with Dave’s trigger in place i can still insert overlapping dates if either the [ValidFrom] or [ValidUntil] are equal.

Short of adding loads more OR clauses to the trigger to account for a matching start OR a matching end OR both - what is the simplest way of tweaking the trigger to prevent this last clause?

To clarify, the overlaps i'm trying to prevent are as follows:

解决方案

I think this condition is more appropriate:

IF EXISTS ( SELECT  * --No need to choose columns in an EXISTS
        FROM    tbl_Example t1
         inner join
                tbl_Example t2
                  on
                     t1.StockCode = t2.StockCode and
                     t1.Type = t2.Type and
                     t1.ValidFrom < t2.ValidTo and
                     t2.ValidFrom < t1.ValidTo and
                     t1.ID <> t2.ID
        where
            t1.ID in (select ID from inserted))
BEGIN
     RAISERROR ('Date range cant overlap existing date ranges for given StockCode and Type', 16, 1)
     ROLLBACK --We're in a trigger, we *must* be in a transaction
END

It uses the simpler condition for detecting overlaps - an overlap exists if both rows start before the other row ends.

相关文章