clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805451 fiddles created (40823 in the last week).

CREATE TABLE dbo.Projects ( ProjectID integer NOT NULL, [Status] character(1) NOT NULL, CONSTRAINT [PK dbo.Projects ProjectID] PRIMARY KEY CLUSTERED (ProjectID), CONSTRAINT [UQ dbo.Projects ProjectID, Status] UNIQUE NONCLUSTERED (ProjectID, [Status]), CONSTRAINT [CK dbo.Projects Status] CHECK ([Status] IN ('A')) ); CREATE TABLE dbo.ProjectPeriods ( ProjectID integer NOT NULL, [Status] character(1) NOT NULL, [Period] character(6) NOT NULL, PeriodCheck varchar(5) NULL, CONSTRAINT [PK dbo.ProjectPeriods ProjectID, Period] PRIMARY KEY CLUSTERED (ProjectID, [Period]), CONSTRAINT [FK dbo.Projects ProjectID, Status] FOREIGN KEY (ProjectID, [Status]) REFERENCES dbo.Projects (ProjectID, [Status]), CONSTRAINT [CK dbo.Projects Period] CHECK ([Period] NOT LIKE '^[0-9]'), CONSTRAINT [CK dbo.Projects PeriodCheck] CHECK (PeriodCheck = 'Later') );
 hidden batch(es)


CREATE TRIGGER [dbo.Projects AI] ON dbo.Projects AFTER INSERT AS BEGIN IF @@ROWCOUNT = 0 RETURN; SET NOCOUNT ON; MERGE dbo.ProjectPeriods AS PP USING ( SELECT I.ProjectID, I.[Status], [Period] = ISNULL(CONVERT(character(6), DATEADD(MONTH, Months.n, CURRENT_TIMESTAMP), 112), '205001'), PeriodCheck = CASE WHEN Months.n IS NULL THEN 'Later' ELSE NULL END FROM Inserted AS I CROSS JOIN ( VALUES (01), (02), (03), (04), (05), (06), (07), (08), (09), (10), (11), (12), (NULL) ) AS Months (n) ) AS PD ON PD.ProjectID = PP.ProjectID AND PD.[Period] = PP.[Period] WHEN NOT MATCHED BY TARGET THEN INSERT (ProjectID, [Status], [Period], PeriodCheck) VALUES (PD.ProjectID, PD.[Status], PD.[Period], PD.PeriodCheck) WHEN NOT MATCHED BY SOURCE AND PP.ProjectID IN (SELECT I.ProjectID FROM Inserted AS I) THEN DELETE; END;
 hidden batch(es)


INSERT dbo.Projects (ProjectID, [Status]) VALUES (1, 'A'); INSERT dbo.Projects (ProjectID, [Status]) VALUES (2, 'A');
2 rows affected
 hidden batch(es)


SELECT * FROM dbo.Projects AS P SELECT * FROM dbo.ProjectPeriods AS PP
ProjectID Status
1 A
2 A
ProjectID Status Period PeriodCheck
1 A 202106
1 A 202107
1 A 202108
1 A 202109
1 A 202110
1 A 202111
1 A 202112
1 A 202201
1 A 202202
1 A 202203
1 A 202204
1 A 202205
1 A 205001 Later
2 A 202106
2 A 202107
2 A 202108
2 A 202109
2 A 202110
2 A 202111
2 A 202112
2 A 202201
2 A 202202
2 A 202203
2 A 202204
2 A 202205
2 A 205001 Later
 hidden batch(es)