How to delete records with FK constraint using Delete Trigger

Question

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 "FK_DimProduct_DimProductSubcategory". 
The conflict occurred in database "AdventureWorksDW2019", table "dbo.DimProduct", 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?

Answer 1

The problem you originally had was that you were not referencing the deleted pseudo-table. You were essentially saying "Oh, delete subcategory 5? Ok, delete all products that have a subcategory!"

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.

Answer 2

In addition to the other answer and comments: Your constraint will give you an error message and halt the operation, and the trigger will never fire. Constraints are checked before triggers are fired.

So if you really want to do this then either have a trigger or a constraint. You can of course have a trigger and a disabled constraint (letting the constraint serve the role of a type of "documentation").

If you choose the trigger route, make sure that the trigger does the right thing, as already been observed by Aaron.