Vryjdfkk

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I can't wrap my head around how the trigger would work. Particularly, how I would work that trigger into my existing one that prevents updates to records, and inserts a new record instead. My real problem is that I do not know how to prevent a Store from having more than one effective date when the finished date is null. (i.e. prevent 2 active records for a store).

This is what I have, but it allows me to insert a new record for a store with a different effective date.

Table Definition:

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

Sample Data:

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)

Instead of Update Trigger:

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

The safest way to do this is to enforce your business rules using the built-in referential integrity constraints, as Alexander Kuznetsov describes in his article, "Storing intervals of time with no overlaps".

Applying the techniques listed there to your sample table results in the following script:

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

Modifications:

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

An attempt to insert the fourth row of sample data now fails with an error message:

INSERT [Z_STORE_TEAM] 

    ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) 

VALUES 

    (2, N'2', '20140201', NULL);

Please read Alex's article to understand how these constraints ensure your table data will always be valid. Having a set of constraints enforce your data integrity means no trigger code is required.

Related article by the same author:

Modifying Contiguous Time Periods in a History Table