3

I am familiar with the ON DELETE CASCADE method, but I don't want to do a real delete, just a soft delete. This is implemented with a column named isDeleted (0/1).

Is there a way for me to cascade this soft delete from a parent to a child? A built in command? or is up to me to write the code?

I am thinking that what I am doing is in fact an update. So I'd be using an ON UPDATE CASCADE, but will that update the same columns on the FKs? or just the primary key?

  • Yes, ON UPDATE CASCADE will update only the columns used in the FK definition. If you make the (ParentID, isDeleted) unique in the parent table and you use that in the FK, it will work (update the isDeleted as well in the child table). But I'm not sure if this is the best way. – ypercubeᵀᴹ Sep 10 '15 at 9:24
1

For what you are describing you would want to use a trigger on the table where you want the soft delete to cascade from.

Here are a few references on triggers:

MSSQLTips

Microsoft's documentation

Triggers have their place but they can quickly get out of hand when triggers fire other triggers or when the trigger execution takes a significant amount of time to complete.

Additionally performance degradation can be a factor with triggers as they happen synchronous when an insert/update/delete statement is made on the table which can extend the execution time of the statement.

Microsoft's Nested triggers

Info from Paul White on triggers

DML triggers for multiple data rows

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.