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

Enforcing unique date range fields in SQL Server 2008(在 SQL Server 2008 中强制执行唯一的日期范围字段)
本文介绍了在 SQL Server 2008 中强制执行唯一的日期范围字段的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

首先,看看这个非常相似的问题: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.

这篇关于在 SQL Server 2008 中强制执行唯一的日期范围字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)