How to delete records with FK constraint using Delete Trigger
The problem you originally had was that you were not referencing the deleted pseudo-table. You were essentially saying &quot;Oh, delete subcategory 5? Ok, delete all products that have a subcategory!&quot;
I think you want this in your trigger:
DELETE p
  FROM dbo.DimProduct AS p
  INNER JOIN deleted AS d
  ON p.ProductSubcategoryKey = d.ProductSubcategoryKey;

I still don't believe you want to delete every product that belongs to a subcategory you are removing, but hopefully this is just an exercise and not real business logic you want to implement.