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!