久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

在 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 中強制執行唯一的日期范圍字段的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個表創建視圖?)
Create calculated value based on calculated value inside previous row(根據前一行內的計算值創建計算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板: 日韩欧美国产精品一区二区 | 黑人巨大精品欧美一区二区免费 | 亚洲成人一区 | 亚洲精品乱码久久久久久按摩观 | 欧美日韩高清在线一区 | 在线精品一区 | 精品一区二区三 | 久草免费在线视频 | 91视频网址 | 国产分类视频 | 中文字幕免费中文 | 在线播放一区二区三区 | 亚洲精品一区二区三区蜜桃久 | 亚洲精品乱码久久久久久9色 | 亚洲精品中文字幕av | 成人亚洲精品 | 欧美性猛片aaaaaaa做受 | 欧美日韩在线观看一区 | 欧美色欧美亚洲另类七区 | 91视频国产区 | 免费观看www| 久久91精品久久久久久9鸭 | 日韩综合一区 | 黑人巨大精品欧美一区二区一视频 | 性欧美精品一区二区三区在线播放 | 日韩在线h | 成人免费福利视频 | 久久国产精品视频免费看 | 丁香五月缴情综合网 | 国产欧美三区 | 亚洲三区视频 | 久久久91精品国产一区二区三区 | 一区二区三区在线观看视频 | 黑人一级黄色大片 | 亚洲av毛片 | 国产一区二区三区 | 黄色一级电影免费观看 | 午夜爽爽爽男女免费观看 | 成年人网站国产 | 亚洲欧美一区二区三区视频 | 亚洲精品无 |