At 05:49 PM 01/09/2002 +0000, you wrote: > > Well here is my thought. As far as my DB is concerned I fully use > > creating a pri key and foreign key relationships where needed on all my > > tables. This helps so much in avoiding someone delteting something from > > one table that relies on something in another table. > >As I said, I do rigorous checks in the CF app for this sort of stuff. >I think my initial reasoning was, I'd like to control error-checking at >the app level. So, if someone tried to delete a record whose PK is >related to several FK's in other tables, I'd do a query to check, and >tell them why they can't delete.
>I just thought, if I rely on the DBMS, it might prevent the deletion, >but the page might break and I don't know what sort of error >message they'd get, whether it would be friendly or not. What >*would* happen if such a delete was attempted on a DB that >had rigorous relationships defined, but where the app had no >checks? What would the user see in the return page? Would it >break the generated CF? Or would the "success" page show, >despite the fact that the DELETE was rolled back (with no >indication that it had failed)? > >I know this is what <CFTRY> is for, but I just opted to spend >time coding app-based checks rather than defining relationships and >coding error handling. Anyways, isn't <CFTRY> kind of generic, >as in, you could catch DB errors but not really know what's >gone wrong? No, cftry (and cfcatch) can become as specific (or generic) as you make it. I suggest you do some reading up on it, most specifically the cfcatch variables (NativeErrorCode and SQLState, perhaps). In situations like you describe, you have many options. Let's start by saying you have two tables: Table1 (Table1ID, Table1data) Table2 (Table2ID, table1ID, table2data) Now you want to delete an entry from table 1. In your description, you say you automatically query table2 to see if it returns any records. If it doesn't, do the delete, otherwise don't. Another option is to automatically delete all entries from table2 first, so you perform two queries: delete from table2 where table1ID = table1ID delete from table1 where table1ID = table1ID Unless I'm completely off-base, you can even implement a trigger at the database level to automatically delete table2 data when something is removed from table1. This method would probably be most preferable, although It is not MS Access-centric. You could also null out the table1ID in table2; although It is not my preferred method. You could also set up your 'delete' to just change a flag. Depending upon the type of data I use this a lot. It makes it *VERY* easy to restore 'lost' data. Just some ideas. -- Jeffry Houser | mailto:[EMAIL PROTECTED] AIM: Reboog711 | ICQ: 5246969 | Fax / Phone: 860-223-7946 -- DotComIt: Database Driven Web Data My Book: Instant ColdFusion 5 | http://www.instantcoldfusion.com My New Book: ColdFusion: A Beginner's Guide February 2002 -- Far Cry Fly, Alternative Folk Rock http://www.farcryfly.com | http://www.mp3.com/FarCryFly ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
