trigger to delete rows if ID doesnt exist in another table

Question

I have this table that adds 10 rows to my table (facttable) for each ID in another table (lets call it dimensiontable). I'm using a Stored Procedure to do this and it starts when a new ID is added to my dimension table. However, sometimes ID's are removed from my dimensiontable. My Stored Procedure does not seem to handle that and the 10 rows in my facttable are not removed. I think a trigger on my facttable is the way to go, but not sure how. Need to check if ID and Period (concat these two) is equal to eachother in both tables. if it does not exist in my dimensiontable, then remove 10 rows from my facttable.

Not sure how to write this, but example follows:

ID	Name	Period
1	First	202203
2	Secon	202203

ID	Name	Period	rows	value
1	First	202203	1	10
1	First	202203	2	10
1	First	202203	3	10
1	First	202203	4	10
1	First	202203	5	10
1	First	202203	6	10
1	First	202203	7	10
1	First	202203	8	10
1	First	202203	9	10
1	First	202203	10	10
2	First	202203	1	10
2	First	202203	2	10
2	First	202203	3	10
2	First	202203	4	10
2	First	202203	5	10
2	First	202203	6	10
2	First	202203	7	10
2	First	202203	8	10
2	First	202203	9	10
2	First	202203	10	10

Now if my ID 2 is deleted from the first table, I would like to have a trigger that removes all rows linked to that ID and Period for my second table.

Thank you so much!

Answer 1

You could add trigger on the dimension (former table of the two), on delete you can delete all rows in the latter table. You could also do this by creating foreign key on the fact-table with cascading delete. Basicaly constraint fk_my_fk foreign key (ID) references dim_table (ID) on delete cascade or there about. By using the foreign key, SQL Server will handle the deletions for you.

Answer 2

This should do what you want. Assuming your unique key is a combination of Id and Period.

It creates a instead of trigger that replace the delete action on the First table so you can make it delete from the second and then the first. Typically you would have a foreign key on the second table back to the first so you need to delete from the second prior to the first.

CREATE TRIGGER TR_D_First
ON dbo.First
INSTEAD OF DELETE
AS 
BEGIN
DELETE S 
  FROM dbo.Second S
  INNER JOIN Deleted D ON D.Id = S.Id and D.Period = S.Period;
 

DELETE F
  FROM dbo.First F
  INNER JOIN Deleted D ON D.Id = F.Id and D.Period = F.Period;

END
GO