Insert into table if column  "Text" for each project 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 &quot;Later&quot; 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!
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&amp;fiddle=8f34f1d9eb00aa1b8603498860a9826d