How to solve ON DELETE CASCADE cycles with triggers on MS SQL Server
I have code that is working okay in PostgreSQL and I now have to port this code to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it:
I have commented out the ON DELETE/UPDATE constraints that were accepted by PostgreSQL, which show the exact behavior I'm trying to reproduce in MS SQL Server, otherwise, I'm getting the error:
So I removed them (equivalent to NO ACTION from the documentation) and decided to go the trigger way (as hinted by several sites) to delete related t_link rows when the related t_parent is deleted:
What I'm trying to have overall is: all t_child records deleted when their related t_parent record is deleted (ON DELETE CASCADE), and t_link records related to deleted t_child deleted as well all t_link records are deleted when their related t_parent record is deleted (ON DELETE CASCADE) t_link.id_child set to NULL when their related t_child record is deleted or deleted as well, if it makes things easier (ON DELETE SET NULL or ON DELETE CASCADE) Then I insert a few test data and try :
I'm estimating that the problem is my trigger is not called because it happens after the delete itself, which fails with the above message; and there is no BEFORE DELETE trigger type (which would sound like something I'd like to have). Now I have to say that the SQL is all generated by a Java JPA-like program that has to cope with the different DBMS (one subclass for PostgreSQL, one for SQL Server, ...) so I should stay generic: I can't put ON DELETE CASCADE constraints on one table and use triggers (or any other method you might know) with others (I could, but at the cost of a code over-complexification that I'm trying to avoid).The SQL Server is a Docker image so I'm not sure I could have debugged the output somewhere (unless in the sqlcmd command). If it's relevant, the version is 2017.The only way out of this I can see is just dropping the reference constraint and handle it all manually with triggers. But then: I see no point in having foreign key constraints?
You're close. AFTER triggers happen after foreign key constraint checking. So you need an INSTEAD OF trigger. That way you can modify the child tables before performing the DELETE on the target table. E.g:
This way t_parent->t_child->t_link uses CASCADE DELETES, and t_parent->t_link is handled by the INSTEAD OF trigger.
How to use Cascade delete in SQL Server You will need to: Drop the existing foreign key constraint, Add a new one with the ON DELETE CASCADE setting enabled. Something like shown below: