DKjHg7Ga

1/****** Object:  Table [PCR].[Z_STORE_TEAM]    Script Date: 05/09/2014 13:05:57 ******/
2IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U'))
3DROP TABLE [Z_STORE_TEAM]
4GO
5
6IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U'))
7BEGIN
8CREATE TABLE [Z_STORE_TEAM](
9    [STORENUM] [int] NOT NULL,
10    [TEAM] [varchar](10) NULL,
11    [EFFECTIVE] [date] NOT NULL,
12    [FINISHED] [date] NULL,
13PRIMARY KEY CLUSTERED 
14(
15    [STORENUM] ASC,
16    [EFFECTIVE] ASC
17)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
18) ON [PRIMARY]
19END
20GO 
21     
22INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date))
23INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'2', CAST(0x81380B00 AS Date), NULL)
24INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'1', CAST(0x01380B00 AS Date), NULL)
25INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'2', CAST(0x20380B00 AS Date), NULL) 
26     
27CREATE TRIGGER [tr_ZStoreTeam_update] 
28   ON  [Z_STORE_TEAM]
29   INSTEAD OF UPDATE
30AS 
31BEGIN
32    -- SET NOCOUNT ON added to prevent extra result sets from
33    -- interfering with SELECT statements.
34    SET NOCOUNT ON;
35
36    -- Insert statements for trigger here
37    INSERT INTO PCR.Z_STORE_TEAM(STORENUM,TEAM,EFFECTIVE)
38    SELECT I.STORENUM,I.TEAM,GETDATE() AS EFFECTIVE
39    FROM inserted I
40    INNER JOIN PCR.Z_STORE_TEAM ST
41        ON I.STORENUM = ST.STORENUM
42
43    UPDATE ST
44    SET FINISHED = GETDATE()
45    FROM PCR.Z_STORE_TEAM ST
46    INNER JOIN inserted I
47        ON ST.STORENUM = I.STORENUM
48        AND ST.EFFECTIVE = I.EFFECTIVE
49END
50
51GO 
52     
53CREATE TABLE [Z_STORE_TEAM](
54    [STORENUM] [int] NOT NULL,
55    [TEAM] [varchar](10) NULL,
56    [EFFECTIVE] [date] NOT NULL,
57    [FINISHED] [date] NULL,
58    PRIMARY KEY CLUSTERED 
59    (
60        [STORENUM] ASC,
61        [EFFECTIVE] ASC
62    )
63) ON [PRIMARY];
64
65INSERT [Z_STORE_TEAM] 
66    ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) 
67VALUES 
68    (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)),
69    (1, N'2', CAST(0x81380B00 AS Date), NULL),
70    (2, N'1', CAST(0x01380B00 AS Date), NULL); 
71     
72-- New column to hold the previous finish date
73ALTER TABLE dbo.Z_STORE_TEAM 
74ADD PreviousFinished date NULL;
75GO
76-- Populate the previous finish date
77UPDATE This
78SET PreviousFinished = Previous.FINISHED
79FROM dbo.Z_STORE_TEAM AS This
80CROSS APPLY
81(
82    SELECT TOP (1) 
83        Previous.FINISHED
84    FROM dbo.Z_STORE_TEAM AS Previous
85    WHERE 
86        Previous.STORENUM = This.STORENUM
87        AND Previous.FINISHED <= This.EFFECTIVE
88    ORDER BY 
89        Previous.FINISHED DESC
90) AS Previous;
91GO
92ALTER TABLE dbo.Z_STORE_TEAM 
93ADD CONSTRAINT UQ_STORENUM_PreviousFinished
94UNIQUE (STORENUM, PreviousFinished);
95GO
96ALTER TABLE dbo.Z_STORE_TEAM
97ADD CONSTRAINT CK_PreviousFinished_NotAfter_Effective
98CHECK (PreviousFinished = EFFECTIVE);
99GO
100ALTER TABLE dbo.Z_STORE_TEAM
101ADD CONSTRAINT UQ_STORENUM_FINISHED
102UNIQUE (STORENUM, FINISHED);
103GO
104ALTER TABLE dbo.Z_STORE_TEAM
105ADD CONSTRAINT FK_STORENUM_PreviousFinished
106FOREIGN KEY (STORENUM, PreviousFinished)
107REFERENCES dbo.Z_STORE_TEAM (STORENUM, FINISHED);
108GO
109ALTER TABLE dbo.Z_STORE_TEAM
110ADD CONSTRAINT CK_EFFECTIVE_Before_FINISHED
111CHECK (EFFECTIVE < FINISHED); 
112     
113INSERT [Z_STORE_TEAM] 
114    ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) 
115VALUES 
116    (2, N'2', '20140201', NULL);