Multi-tiered record deletion

[mrb783]

Hi, I have a database with a 5-tier table hierarchy. My database users are not allowed to go in and delete records and as such, I have them "request" that they be deleted, by selecting a specific checkbox for each record they wish to have deleted. This doesn't happen very often, so there hasn't been a very large problem with this.

That said, however, I am having issues arise when the users do not check the deletion request box for all of the associated child records. When I attempt to process the deletion request (just a simple form button activated delete query), I get a message saying it can't delete them due to linked records.

What I want to know is whether or not there is a way to delete all child records associated with the parent record starting from the bottom up (i.e. if the top level parent record is set for deletion, it will start at the bottom and delete all associated records, then the next tier, and so on). Any thoughts?

 

[dkinley]

In your relationship properties (double-click on the line connecting your tables), look at "Enforce Referential Integrity".

If you check the "Cascade Delete Related Records" then once you delete the top tier record - it will cascade down and delete all of the child records for you.


-dK

 

[gemma-the-husky]

but it is very dangerous to have cascading deletes.

it is normally far better merely to mark the deleted items as deleted, with a yesno flag, and base the forms on queries that select only the non-deleted items

once you delete you cannot reinstate if you change your mnd, and you lose all your transaction history.