or
sql-server
RubberDuck imported from SE
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](https://dba.stackexchange.com/a/56605/37401), 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
Top Answer
Paul White
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"][1].

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

![Error message][2]

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][3]


  [1]: https://web.archive.org/web/20180424214356/http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx
  [2]: https://i.stack.imgur.com/JRjsX.png
  [3]: https://www.simple-talk.com/content/article.aspx?article=1191
What is the correct way to ensure unique entries in a temporal database design?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.