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

There are a couple of issues here.

-- The normal way to implement what you are talking about is with a cascading foreign-key constraint:

ALTER TABLE childtbl ADD CONSRAINT fk_child_parent
   FOREIGN KEY (parentcol) REFERENCES parent (parentcol)
   ON UPDATE CASCADE ON DELETE CASCADE

-- You have an AFTER trgger, which means that it fires after the statement has completed. Constraints are checked before triggers fire. Whence the error. You could code this as an INSTEAD OF trigger, but you should learn to master AFTER trigger first, so we skip that for now. Better is to disable the constraint to be able to conclude this particular exercise.

-- You trigger is incorrectly written. If you would disable the constraint and test, you would find that in deletes all products with a subcategory - except in the case when you delete all product sub-categories.

There are two problems in your DELETE statement, one related to your trigger and one not. Let's start with the one that is unrelated to the trigger. You have:

DELETE FROM DimProduct WHERE (DimProduct.ProductSubcategoryKey IN (SELECT DimProduct.ProductSubcategoryKey FROM DimProductSubcategory))

In the subquery, you are referring to the outer table. Thus, you essentially have

DELETE DimProduct WHERE ProductSubcategoryKey = ProductSubcategoryKey

Except for the case when the DimProductSubcategory table is empty.

On its own - and outside a trigger - this should be:

DELETE DimProduct 
FROM  DimProduct P 
WHERE P.ProductSubcategoryKey IN (SELECT PS.ProductSubcategoryKey FROM DimProductSubcategory PS)

Here I have introduced aliases. You should start using aliases, as this makes your queries shorter and easier to read. Note that I'm now taking the ProductSubcategoryKey column from DimProductSubcategory.

Let's now look at the trigger-related error which also SQLZealots touched at in the answer above. In a trigger you have access to two virtual tables inserted and deleted. They have the same schema as the parent table of the trigger. The inserted table holds the row inserted by an INSERT statement and for an UPDATE statement it holds the after-image of the updated rows. The deleted table holds the rows deleted by a DELETE statement and the before-image of the rows affected by an UPDATE statement. Thus, you need to use deleted here, so that you only work with the categories that were actually deleted.

Answer 2

Hi @ruslaniv ,

I tried to delete several records from DimProductSubcategory or DimProduct tables in AdventureWorksDW2019 database, I got below errors:

delete from DimProductSubcategory where ProductSubcategoryKey=14  
--The DELETE statement conflicted with the REFERENCE constraint "FK_DimProduct_DimProductSubcategory". The conflict occurred in database "AdventureWorksDW2019", table "dbo.DimProduct", column 'ProductSubcategoryKey'.  
  
delete from DimProduct where ProductSubcategoryKey=14  
--The DELETE statement conflicted with the REFERENCE constraint "FK_FactProductInventory_DimProduct". The conflict occurred in database "AdventureWorksDW2019", table "dbo.FactProductInventory", column 'ProductKey'.  
  
delete from DimProduct where ProductSubcategoryKey=1  
--The DELETE statement conflicted with the REFERENCE constraint "FK_FactInternetSales_DimProduct". The conflict occurred in database "AdventureWorksDW2019", table "dbo.FactInternetSales", column 'ProductKey'.  

It seems that you could not resolve this issue only by deleting the records in DimProductSubcategory or DimProduct tables.

Normally, there are two options in your situation.

1. Deleting In Correct Order.
2. Use ON DELETE CASCADE.

Note: ON DELETE CASCADE can be dangerous, so use with care.

Alter trigger like below:

DELETE a  
  FROM dbo.DimProduct AS a  
  INNER JOIN deleted AS b  
  ON a.ProductSubcategoryKey = b.ProductSubcategoryKey;  

Modify the constraint like below:

ALTER TABLE DimProduct  
  DROP CONTRAINT FK_DimProduct_DimProductSubcategory;  
ALTER TABLE DimProduct  
  ADD CONSTRAINT FK_DimProduct_DimProductSubcategory  
    FOREIGN KEY ( ProductSubcategoryKey )  
    REFERENCES DimProductSubcategory ( ProductSubcategoryKey )  
    ON DELETE CASCADE;  

Please refer more details in the first answer in below link and check whether it is helpful to you.

DELETE statement conflicted with the REFERENCE constraint

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi,

Is your FK with cascade option?

Answer 4

Your trigger code is scary. You are not using deleted tables.

Change your code to use deleted tables, it should be good.
Your ref:
https://www.mssqltips.com/sqlservertip/2342/understanding-sql-server-inserted-and-deleted-tables-for-dml-triggers/

Just curious, why did not you try with CASCADE DELETE?

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
Blog
LinkedIn

Answer 5

In addition to Erland's detailed answer:

It is a dimensional model.
You normally don't delete in dimensions. They are populated by ETL processes, which normally ensure referential (RI) in these ETL processes. This is also the preferred/recommended approach by Kimball.

Thus, when you really need to delete dimensional entries manually, then consider using a stored procedure to do this.