Temporary ON DELETE CASCADE

Question

I am using SQL Server 2005.

Our application is almost never deleted without its logical removal, and therefore we do not need cascading deletions.

In fact, its quite comfortable knowing that external constraints can give us some protection against accidental removal statements.

However, sometimes I need to delete a top-level table and all its children. At the moment, I am doing this with several DELETE statements in the writing order, and it becomes very large, complex and impossible to update the script.

I am wondering if there is a way to automatically enable cascading deletes for all foreign keys in the database, perform my top-level delete and re-enable them again?

Answer 1

For another question here, I wrote a script that should automatically generate deletions.

Answer 2

How to write a couple of scripts - one of them is started manually when you are going to delete records that allow you to use the cascade for deletion for restrictions, and then another one that you run after the deletion is completed to disable them and return things to normal?

Answer 3

: INSTEAD OF TRIGGER , , child-record-delete