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
Best Answer
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:
Modifications:
An attempt to insert the fourth row of sample data now fails with an error message:
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