0

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?

5
  • 1
    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? Sep 19, 2020 at 19:42
  • 1
    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? Sep 19, 2020 at 19:47
  • @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.
    – ruslaniv
    Sep 20, 2020 at 11:12
  • Why not use a constraint that's defined as on delete cascade?
    – user1822
    Sep 20, 2020 at 17:36
  • 1
    @a_horse_with_no_name There are a bunch of reasons for me, some are laid out here. Sep 21, 2020 at 1:08

2 Answers 2

2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.