trigger to delete rows if ID doesnt exist in another table



  • 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!



  • 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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2