Sql-server – Insert into table if column <> “Text” for each project

insertsql servertrigger

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!

Best Answer

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

Related Question