SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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')
);
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;
INSERT dbo.Projects (ProjectID, [Status]) VALUES (1, 'A');
INSERT dbo.Projects (ProjectID, [Status]) VALUES (2, 'A');
2 rows affected

SELECT * FROM dbo.Projects AS P
SELECT * FROM dbo.ProjectPeriods AS PP


ProjectID
Status


1
A

2
A




ProjectID
Status
Period
PeriodCheck


1
A
202309
null

1
A
202310
null

1
A
202311
null

1
A
202312
null

1
A
202401
null

1
A
202402
null

1
A
202403
null

1
A
202404
null

1
A
202405
null

1
A
202406
null

1
A
202407
null

1
A
202408
null

1
A
205001
Later

2
A
202309
null

2
A
202310
null

2
A
202311
null

2
A
202312
null

2
A
202401
null

2
A
202402
null

2
A
202403
null

2
A
202404
null

2
A
202405
null

2
A
202406
null

2
A
202407
null

2
A
202408
null

2
A
205001
Later

ProjectID	Status	Period	PeriodCheck
1	A	202309	null
1	A	202310	null
1	A	202311	null
1	A	202312	null
1	A	202401	null
1	A	202402	null
1	A	202403	null
1	A	202404	null
1	A	202405	null
1	A	202406	null
1	A	202407	null
1	A	202408	null
1	A	205001	Later
2	A	202309	null
2	A	202310	null
2	A	202311	null
2	A	202312	null
2	A	202401	null
2	A	202402	null
2	A	202403	null
2	A	202404	null
2	A	202405	null
2	A	202406	null
2	A	202407	null
2	A	202408	null
2	A	205001	Later