John W. Colby
jcolby at colbyconsulting.com
Sat Feb 8 18:39:00 CST 2003
Stuart, No, you ALWAYS turn on referential integrity. That enforces that there are no orphans. Having done that you now need to deal with deletes. Parents can't be deleted until children are. Thus the delete queries from child up to parent in my example. Or... You use another method. Gustav described one based on dates. I am fascinated by this method and will probably look at it more closely in the future. I currently use a method based on two Boolean flags - active / trash. In my systems, when a record is created, the active flag is set true and the trash flag false (at the database level using default values). I set referential integrity and set cascade delete off. Now, in my forms, I trap the error generated by Jet whenever a user tries to delete a record with child records. The error handler clears the active flag and sets the trash flag for that record, then requeries the form. The forms only show active records (a filter in the form's query) so when the active flag is cleared by the error handler, and the form re-queries, the record "disappears". It appears to actually delete. Of course it doesn't really, it just appears that way. Thus in my system I can do several things. When the delete error is generated by JET, I can simply inform the user that (s)he has no rights to delete that record and do nothing. Or I can clear the active flag and requery the form. APPARENTLY delete the record. In either case, the record is never deleted. At worst the database administrator can go find the record and "undelete" it by clearing trash and setting active. The point is, that as a developer you have several choices. One is to simply throw up your hands and say "I warned them" and allow anyone to delete anything they want (cascade delete on). Another is to prevent the deletes (of recs with children at any rate) by turning off cascade delete. That will, 100% of the time, prevent such deletes. Of course that puts the burden on you (the developer) to do something with the user's delete attempt. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Stuart McLachlan Sent: Saturday, February 08, 2003 5:54 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Cascade-delete (was: Estimating Help) > Rocky, > > What I was saying the other day is simply that cascade delete has no ability > to assess the "rights" of the person deleting something. Quite correct but neither can a simple delete deternine the rights of the person doing it. If someone can delete a parent record, do you want a load of orphans lying around messing up your summarising queries? Can anyone give me an example of where you would want to delete a parent and not it's children? The answer to stopping "manservants" et al from damaging your database is to control who can do ANY deleting, not to allow people to *only* delete some of the records in a relationship. -- Lexacorp Ltd http://www.lexacorp.com.pg Information Technology Consultancy, Software Development,System Support. _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com