How to delete records with FK constraint using Delete Trigger I'm learning MS SQL Server and trying to implement a trigger to delete records in the child table  if the record in the parent table is deleted. I'm using AdventureWorksDW database provided my Microsoft.
I have two tables DimProductSubcategory and DimProduct. So if I delete a product category in DimProductSubcategory, all related records in DimProduct should also be deleted.
So I have created a trigger:
CREATE trigger on_delete_trigger
    on DimProductSubcategory
    after delete
    as
    begin
        SET NOCOUNT ON;
        DELETE FROM DimProduct WHERE (DimProduct.ProductSubcategoryKey IN (SELECT DimProduct.ProductSubcategoryKey FROM DimProductSubcategory))
    end

But when I try to delete a record in DimProductSubcategory I get:
The DELETE statement conflicted with the REFERENCE constraint &quot;FK_DimProduct_DimProductSubcategory&quot;. 
The conflict occurred in database &quot;AdventureWorksDW2019&quot;, table &quot;dbo.DimProduct&quot;, column 'ProductSubcategoryKey'.

I understand the meaning of the error message, but I do not understand the reason for it. I thought the trigger was supposed to delete all child records so that I can delete the parent record without violating data integrity.
Although I'm not 100% sure I got my DELETE statement right.
So how can I implement a trigger to delete child records when a parent record is deleted?
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.