Checking rule violations before deleting a record

Question

I would like to implement a "soft-delete" scheme for a number of entities in my SQL Server 2005 database. By this, I mean I would like to delete an row from a table if there are no referential integrity rule violations, otherwise I will set a flag on the record to signify it has been deleted. The table I wish to enforce this "soft-delete" pattern must have "No Action" applied as the "Insert/Update Specification".

How can I check to see if the delete I want to run will violate foreign key constraints?

I do not want to capture exceptions - I would like to explicitly check to see if rules would be violated. I also do not want to have to manually check via SELECT statements (maintenance nightmare). I would prefer a solution in T-SQL; but I am using Entity Framework, so it would be possible to utilize an API if one exists for this task.

Note that there is a similar question stated here, but the answers presented do not suit my requirements.

Answer 1

You could use an INSTEAD OF DELETE trigger on the target tables to run select statements to check for FK violations before carrying out the delete if none are found. This will enable you to encapsulate all the logic in the database, although performance may be a problem if you have a lot of deletes, a large dataset or many foreign keys. It would be possible to write generic code which uses the database metadata to build the necessary queries dynamically, rather than coding everything by hand, if you feel this presents an unacceptable maintenance problem.

The other, perhaps simpler, option would be to implement soft delete for all deletes, and then to include a scheduled maintenance task which converts all the soft deletes which can be safely removed into hard deletes. It may be acceptable not to carry out the scheduled hard deletions, depending on the size of the dataset and the number of deletes.

EDIT

John Sansom's answer is a better implementation of the trigger solution.

Answer 2

Similar to Ed Harper’s solution, I would also suggest that you use an INSTEAD OF DELETE TRIGGER however, we differ in our solution in that I propose you actually configure your database to enforce the desired integrity checking/rules.

This way, when a delete operation cannot complete successfully within your Trigger code, due to constraint violations, you can mark the record (“soft delete”) rather than actually delete it.

Alternatively, should no violations occur, the delete operation will simply complete successfully.

This implementation ensures that the DBMS handles the entire responsibility of managing database integrity, which of course should be the desired scenario.

Make sense?