0

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!

2 Answers 2

0

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.

4
  • Thanks! Need to have the trigger on my facttable. I'll do some googling on the cascading delete, as I have never seen that before..
    – Clueless
    Mar 30 at 11:11
  • If you want to have a trigger, which is triggered when a record in deleted in the dimension table, then the trigger must be on the dimension table. That is the way the triggers are designed. But you would put a foreign key with cascading delete on the fact, since the ID column there is referencing the ID column in the dimension. Then the db engine will check the fact table when there is a delete in the dimension and act according to the foreign key cascading rule.
    – Björgvin
    Mar 30 at 11:15
  • @Clueless Why do you need to have a trigger? Mar 30 at 23:43
  • You don't NEED a trigger, but your task CAN be solved with a trigger. If you decide to use a trigger, that trigger must be triggered when a deletion happens in the dimension table. That is the way to solver your task with a trigger. An alternative approach is to use foreign key.
    – Björgvin
    Apr 6 at 15:03
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.