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