I have to tables. One dimension table with projects and one fact table with projects, value and a period(month).
Looking to add 12 new rows pr project ID into my fact table, when a new project is added to the dimension table. The 12 rows should be the next 12 months. (always the month following the current)
The issue I have is how to write a SQL statement that I can use with a trigger to make this work. I have tried some versions on INSERT INTO, but I'm not experienced enough to know have to write this statement.
example: project 2 has been added to dim table.
project | status |
---|---|
1 | A |
2 | B |
Then create a trigger that adds these 12 new rows in my facttable
project | status | Period | Value |
---|---|---|---|
2 | B | 6/1/2021 | NULL |
2 | B | 7/1/2021 | NULL |
2 | B | 8/1/2021 | NULL |
2 | B | 9/1/2021 | NULL |
2 | B | 10/1/2021 | NULL |
2 | B | 11/1/2021 | NULL |
2 | B | 12/1/2021 | NULL |
2 | B | 1/1/2022 | NULL |
2 | B | 2/1/2022 | NULL |
2 | B | 3/1/2022 | NULL |
2 | B | 4/1/2022 | NULL |
2 | B | 5/1/2022 | NULL |
Any suggestions on how to write this statement?
Thank you so much!