問題描述
首先，看看這個非常相似的問題: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 中強制執行唯一的日期范圍字段的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！