Delete trigger to delete child records-CodePudding

So, I have two tables. One is sales_order (no_so VARCHAR PRIMARY KEY) The other is status_so (no_so REFERENCES to sales_order)

I want the no_so from status_so is deleted if I delete the no_so in sales_order. So, I create the trigger to solve the problem.

CREATE TRIGGER trg_sales_order
ON sales_order
FOR DELETE
AS
   DELETE FROM status_so 
   WHERE status_so.no_so IN (SELECT deleted.no_so FROM deleted)

after that I run this code

DELETE FROM sales_order WHERE no_so = 'SO004'

and I still got an error, it says

The DELETE statement conflicted with the REFERENCE constraint "FK_status_so_sales_order". The conflict occurred in database "db", table "dbo.status_so", column 'no_so'.

CodePudding user response：

From the docs:

OR | AFTER FOR or AFTER specifies that the DML trigger fires only when all operations specified in the triggering SQL statement have launched successfully. All referential cascade actions and constraint checks must also succeed before this trigger fires.

You need to create instead of delete trigger and handle the deletions from the both table in the correct order there.