Pragmatically Cascade Delete

[kd2017]

I'm wondering if there are any tricks to pragmatically cascade or recursively delete a record and all of its "children" (and their children, etc)?



In *most* of my relationships w/ referential integrity I have left cascade delete unchecked on purpose. I've done this because I'm paranoid and obviously to prevent accidental data loss.

I've got a situation, a command button, where if I really really really want to I can delete a [job]. When I click this button its subroutine performs a series of 15 different sql calls to find and delete related data in 15 different tables (as of right now). Is there a more efficient way to do this? My only other thought was to pragmatically change the constraints to cascade delete, delete once, then change the constraints back. But changing ALL of the constraints would be a chore too.

As far as children, or nesting goes in my situation a [job] will have related data as much as 5 relationships deep. (I'm not sure how the right terms here... table Jobs has many B which has many C which has many D which as many E which has many F.. does my babble make sense?)

[CJ_London]

only by doing what you are doing. I agree with your policy to not set 'cascade delete'. Much better to keep full control. However I also have a policy of not deleting data (except under a development/test environment).

Instead I have a 'mark deleted' flag (might be a Boolean, might be an end date, status field or something similar) which is applied in queries to not be returned. Depending on how your db works, you only need to update this for the parent record - and bear it in mind when creating your queries to exclude them.

If you are archiving - I don't actually copy data to an archive then delete, I create a new db and copy across the records I want to retain/continue to use based on some business rule (and the mark deleted flag) - in much the same way a compact and repair works.

[kd2017]

I was afraid of that. Yeah, the deleting routines are *mostly* for testing/development. I do have [IsClosed] flags on certain tables for hiding some things from queries, it'll function like an archiving flag I guess.

That's a cool idea on your archiving approach. I'll keep that in mind.