Jaki jest właściwy sposób, aby zapewnić unikalne wpisy w projekcie tymczasowej bazy danych?

Mam problem z zaprojektowaniem tymczasowej bazy danych. Muszę wiedzieć, jak upewnić się, że mam tylko jeden aktywny rekord dla dowolnego przedziału czasowego dla sklepu. Przeczytałem tę odpowiedź , ale obawiam się, że nie mogę owinąć głowy tym, jak zadziałałby spust. W szczególności sposób, w jaki działałbym, który uruchamia się do mojego istniejącego, który zapobiega aktualizowaniu rekordów i zamiast tego wstawia nowy rekord. Moim prawdziwym problemem jest to, że nie wiem, jak uniemożliwić Sklepowi posiadanie więcej niż jednej daty wejścia w życie, gdy data zakończenia jest zerowa. (tzn. zapobiec 2 aktywnym rekordom dla sklepu).

To właśnie mam, ale pozwala mi wstawić nowy rekord dla sklepu z inną datą wejścia w życie.

Definicja tabeli:

/****** 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

Przykładowe dane:

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)

Zamiast wyzwalacza aktualizacji:

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

Najbezpieczniejszym sposobem na to jest egzekwowanie reguł biznesowych przy użyciu wbudowanych ograniczeń integralności referencyjnej, jak opisuje Alexander Kuzniecow w swoim artykule „Przechowywanie przedziałów czasu bez nakładania się” .

Zastosowanie wymienionych tam technik do przykładowej tabeli daje następujący skrypt:

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);

Modyfikacje:

-- 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);

Próba wstawienia czwartego wiersza przykładowych danych kończy się niepowodzeniem i pojawia się komunikat o błędzie:

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

Przeczytaj artykuł Alexa, aby zrozumieć, w jaki sposób te ograniczenia zapewniają, że dane w tabeli będą zawsze aktualne. Posiadanie zestawu ograniczeń wymusza integralność danych oznacza, że ​​kod wyzwalający nie jest wymagany.

Powiązany artykuł tego samego autora:

Modyfikowanie ciągłych przedziałów czasowych w tabeli historii