Insert into table if column <> "Text" for each project

Question

I have created two triggers to insert into fact table if a new row is inserted into dim table. One checks if Project exists already and if not, then query will be triggered and adds 12 new rows per project. I have tested this and works fine. Now I need one more trigger make an insert check check if project exists, then don't trigger, but in addition this insert query need to check if Periodcheck is equal to "Later" for each project, if a row like that doesn't exists, then it should add a new row in my fact table. Tried a few different things, but can't seem to put it all together without creating duplicate rows for project and Periodcheck.

dimtable table looks like this (lets pretend project 2 is just added):

ProjectID	Status
1	A
2	A

Factable like this where project 1 is correct, but project 2 is missing the last row wiht Periodcheck = 'Later':

ProjectID	Status	Period	Periodcheck
1	A	202106	NULL
1	A	202107	NULL
1	A	202108	NULL
1	A	202109	NULL
1	A	202110	NULL
1	A	202111	NULL
1	A	202112	NULL
1	A	202201	NULL
1	A	202202	NULL
1	A	202203	NULL
1	A	202204	NULL
1	A	202205	NULL
1	A	205001	Later
2	A	202106	NULL
2	A	202107	NULL
2	A	202108	NULL
2	A	202109	NULL
2	A	202110	NULL
2	A	202111	NULL
2	A	202112	NULL
2	A	202201	NULL
2	A	202202	NULL
2	A	202203	NULL
2	A	202204	NULL
2	A	202205	NULL

This query below is supposed to only add one row with Periodcheck = 'Later' for projet 2, since that row does not exist in my facttable.

CREATE TRIGGER project_insert
ON dimtable
AFTER INSERT
AS
BEGIN
Insert into facttable (ProjectID, Status, Period, Periodcheck)
SELECT 
   ProjectID
 , Status
 , Period = '205001'
 , Periodcheck = 'Later' 
FROM dimtable d
  WHERE EXISTS(Select ProjectID FROM facttable f
        WHERE d.ProjectID = f.ProjectID)
  and
   NOT EXISTS(SELECT Periodcheck FROM 
        facttable f
      WHERE t2.Periodcheck = 'Later')
END

End goal is to add this row to only projects that does not already have this row in my facttable. Any suggestions on how to change the query to do this? It runs, but I have tested by adding a project to dimtable and this trigger does not add my desired row.

Thank you so much!

Answer 1

You can do everything needed in a single trigger.

For example, given the tables:

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')
);

The following trigger creates the twelve periods and the 'Later' entry if they are missing. It also removes any entries that shouldn't be there.

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;

Executing:

INSERT dbo.Projects (ProjectID, [Status]) VALUES (1, 'A');
INSERT dbo.Projects (ProjectID, [Status]) VALUES (2, 'A');

or the equivalent:

INSERT dbo.Projects 
    (ProjectID, [Status]) 
VALUES 
    (1, 'A'),
    (2, 'A');

Produces the required outcome:

ProjectID	Status
1	A
2	A

ProjectID	Status	Period	PeriodCheck
1	A	202106	NULL
1	A	202107	NULL
1	A	202108	NULL
1	A	202109	NULL
1	A	202110	NULL
1	A	202111	NULL
1	A	202112	NULL
1	A	202201	NULL
1	A	202202	NULL
1	A	202203	NULL
1	A	202204	NULL
1	A	202205	NULL
1	A	205001	Later
2	A	202106	NULL
2	A	202107	NULL
2	A	202108	NULL
2	A	202109	NULL
2	A	202110	NULL
2	A	202111	NULL
2	A	202112	NULL
2	A	202201	NULL
2	A	202202	NULL
2	A	202203	NULL
2	A	202204	NULL
2	A	202205	NULL
2	A	205001	Later

Online demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8f34f1d9eb00aa1b8603498860a9826d