在時態數據庫設計中確保唯一條目的正確方法是什麼？

我在設計時態數據庫時遇到問題。我需要知道如何確保在任何給定時間範圍內我只有一個活動記錄。我已經閱讀了這個答案，但恐怕我無法理解觸發器的工作原理。特別是，我將如何將觸發器工作到我現有的觸發器中，以防止更新記錄，而是插入一條新記錄。我真正的問題是，當完成日期為空時，我不知道如何防止商店擁有多個有效日期。（即防止商店有 2 條活動記錄）。

這就是我所擁有的，但它允許我為具有不同生效日期的商店插入新記錄。

表定義：

/****** Object:  Table [PCR].[Z_STORE_TEAM]    Script Date: 05/09/2014 13:05:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U'))
DROP TABLE [Z_STORE_TEAM]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U'))
BEGIN
CREATE TABLE [Z_STORE_TEAM](
   [STORENUM] [int] NOT NULL,
   [TEAM] [varchar](10) NULL,
   [EFFECTIVE] [date] NOT NULL,
   [FINISHED] [date] NULL,
PRIMARY KEY CLUSTERED 
(
   [STORENUM] ASC,
   [EFFECTIVE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

樣本數據：

INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date))
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'2', CAST(0x81380B00 AS Date), NULL)
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'1', CAST(0x01380B00 AS Date), NULL)
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'2', CAST(0x20380B00 AS Date), NULL)

而不是更新觸發器：

CREATE TRIGGER [tr_ZStoreTeam_update] 
  ON  [Z_STORE_TEAM]
  INSTEAD OF UPDATE
AS 
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   -- Insert statements for trigger here
   INSERT INTO PCR.Z_STORE_TEAM(STORENUM,TEAM,EFFECTIVE)
   SELECT I.STORENUM,I.TEAM,GETDATE() AS EFFECTIVE
   FROM inserted I
   INNER JOIN PCR.Z_STORE_TEAM ST
       ON I.STORENUM = ST.STORENUM

   UPDATE ST
   SET FINISHED = GETDATE()
   FROM PCR.Z_STORE_TEAM ST
   INNER JOIN inserted I
       ON ST.STORENUM = I.STORENUM
       AND ST.EFFECTIVE = I.EFFECTIVE
END

GO

最安全的方法是使用內置的參照完整性約束來強制執行您的業務規則，正如 Alexander Kuznetsov 在他的文章“儲存沒有重疊的時間間隔”中所描述的那樣。

將此處列出的技術應用於您的範例表會產生以下腳本：

CREATE TABLE [Z_STORE_TEAM](
   [STORENUM] [int] NOT NULL,
   [TEAM] [varchar](10) NULL,
   [EFFECTIVE] [date] NOT NULL,
   [FINISHED] [date] NULL,
   PRIMARY KEY CLUSTERED 
   (
       [STORENUM] ASC,
       [EFFECTIVE] ASC
   )
) ON [PRIMARY];

INSERT [Z_STORE_TEAM] 
   ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) 
VALUES 
   (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)),
   (1, N'2', CAST(0x81380B00 AS Date), NULL),
   (2, N'1', CAST(0x01380B00 AS Date), NULL);

修改：

-- New column to hold the previous finish date
ALTER TABLE dbo.Z_STORE_TEAM 
ADD PreviousFinished date NULL;
GO
-- Populate the previous finish date
UPDATE This
SET PreviousFinished = Previous.FINISHED
FROM dbo.Z_STORE_TEAM AS This
CROSS APPLY
(
   SELECT TOP (1) 
       Previous.FINISHED
   FROM dbo.Z_STORE_TEAM AS Previous
   WHERE 
       Previous.STORENUM = This.STORENUM
       AND Previous.FINISHED <= This.EFFECTIVE
   ORDER BY 
       Previous.FINISHED DESC
) AS Previous;
GO
ALTER TABLE dbo.Z_STORE_TEAM 
ADD CONSTRAINT UQ_STORENUM_PreviousFinished
UNIQUE (STORENUM, PreviousFinished);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT CK_PreviousFinished_NotAfter_Effective
CHECK (PreviousFinished = EFFECTIVE);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT UQ_STORENUM_FINISHED
UNIQUE (STORENUM, FINISHED);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT FK_STORENUM_PreviousFinished
FOREIGN KEY (STORENUM, PreviousFinished)
REFERENCES dbo.Z_STORE_TEAM (STORENUM, FINISHED);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT CK_EFFECTIVE_Before_FINISHED
CHECK (EFFECTIVE < FINISHED);

現在嘗試插入第四行範例數據失敗並顯示錯誤消息：

INSERT [Z_STORE_TEAM] 
   ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) 
VALUES 
   (2, N'2', '20140201', NULL);

請閱讀 Alex 的文章以了解這些約束如何確保您的表數據始終有效。擁有一組約束來強制您的數據完整性意味著不需要觸發程式碼。

同一作者的相關文章：

修改歷史表中的連續時間段

引用自：https://dba.stackexchange.com/questions/64862