Have you considered simply not allowing deletes and instead allow for an archiving option? Archiving would allow you to keep records, history and other information while making the record 'inaccessible' through default views (or filters).

If you need to delete the record for privacy reasons, you might consider an option (stored procedure?) to anonymize the contact details and then archive the record.

The big question is here: why do you need to delete records, and why do you need to reassign the childrecords?

Another option is on delete to try the delete, if it fails on integrity constraints, prompt the user for the new 'parent' of the child records and reassign, then delete the old parent record. The downside of course is that it could be easy to assign the record to the wrong 'new' parent.

Yet another option is to only allow deletes for records with certain implicit or explicit state, where the workflow (or workprocedure) for working with the records gets the record naturally to the point it can actually be deleted. In that way the deletion is embedded in the rest of the workprocedure and absence of delete options in other states is understandable.

Mark

> I'm wondering if there is a 'best practice' for doing this. I have a
> database full of contact records. Of these, many have related tables of
> data that I can't delete the contact until those related records are
> re-assigned to other parties. Consequently a Cascade delete rule won't
> work here.
>
> But rather than giving the user the ability to 'try' to delete the
> record and be told that they can't because of a violation of a
> referential integrity rule, I'd like to be more pro-active with this and
> only give them a delete option of the contact record has no related data
> that would stop a delete from occuring.
>
> Is there is a good and fast way to determine if one of these records
> could be deleted other than giving the user the ability to try and then
> be told they can't? I'm basically trying to simplify their world a bit
> by being proactive and telling them up-front that a record can or cannot
> be deleted as it stands.
>
> All suggestions, comments, flames, etc. are actually greatly appreciated.

--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01