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?

• Aaron Bertrand: You can create a stored procedure to process your deletes in the necessary order (delete child rows first), use an instead of trigger to do the same, or use CASCADE (not recommended IMHO). You can't wait to delete child rows until after the parent row no longer exists. Though I don't think you got your DELETE statement right anyway, think about what just this part is saying: WHERE (DimProduct.ProductSubcategoryKey IN (SELECT DimProduct.ProductSubcategoryKey - don't you think that will match every row in the table and not really consider what's in DimProductSubcategory at all?
• Aaron Bertrand: Also, it seems your logic is not entirely intuitive to me. When you delete a sub-category you want to delete all the products in that sub-category? Is this something you'll often want to do? How often do you eliminate a category and also burn all the inventory you have? Usually wouldn't it be changing the category or just no longer including that specific sub-category?
• ruslaniv: @AaronBertrand I would like to implement a trigger since it will automatically fire when a delete event occurs. Yes, I understand that the example is a little bit contrived but my goal here was to study triggers and data integrity, in production I would probably set the subcategory to NULL or something like that. I agree, I need to think about that DELETE statement, about how I reference the subcategory that was deleted so I can proceed deleting all the items in the Product table with THIS subcategory.
• user1822: Why not use a constraint that's defined as on delete cascade?
• Aaron Bertrand: @a_horse_with_no_name There are a bunch of reasons for me, some are laid out here.

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.