In my experience, this would be better done in a single stored procedure call (which would then call any other required stored procs). SQL Server supports nested transactions, so each of your 'child' stored procs can also use transactions if required.
If you are writing this from scratch, put as much logic as you can into the stored procs (afterall, the database server is meant to handle data for you, whereas your web server is meant to present web pages). As for CFTRANSACTION, we are unclear on how it could handle a rollback if you have stored procs within it that have committed their SQL Transactions. Until I hear more, I'm recommending to my team to NOT use CFTransaction for that reason. The data for our current app is critical, and we have to stick with methods we know to work well. Let me know if you hear any more on this. I'm not getting the impression that I'll see lots on this from the list. Shawn Grover -----Original Message----- From: Bill Grover [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 5:48 AM To: CF-Talk Subject: RE: CFTransactions and SQL Transactions? Shawn, I don't have an answer but am anxiously awaiting one. I have a case where in order to get some records into our SQL database I execute a SP to enter a blank record and then call several SP's to update different fields in the record with values. I need to wrap this in a <cftransaction> and am just trying to gather some more information before doing it. ______________________________________________________ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX: 301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299 WWW: http://www.euservices.com ______________________________________________________ > -----Original Message----- > From: Shawn Grover [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 11, 2002 3:03 PM > To: CF-Talk > Subject: CFTransactions and SQL Transactions? > > > This is a repost cuz I think I got lost in the shuffle on > Friday, and no-one > has responed yet. > > TIA. > > -----Original Message----- > From: Shawn Grover [mailto:[EMAIL PROTECTED]] > Sent: Friday, March 08, 2002 9:54 AM > To: CF-Talk > Subject: CFTransactions and SQL Transactions? > > > I'm looking for more information about using <CFTransaction>. > My personal opinion is that it's better to use SQL Server transactions > within our stored procedures (cuz we are using SQL 2000 as > our back end), > and transfer as much business logic as possible to the stored procs. > > However, in a few cases, we've seen the requirement for > something like this: > > <cftransaction action="BEGIN"> > <cfstoredproc></cfstoredproc> > <cfif somecondition> > <cfstoredproc></cfstoredproc> > <cfelse> > <cfstoredproc></cfstoredproc> > </cfif> > </cftransaction> > > My concerns are that this may cause problems if the stored procedures > themselves contain transaction processing. And I need to make a > recommendation based on solid information on whether to allow > this process, > or force the developers to take the time to wrap this logic within one > stored proc call. (of course, they can call existing stored > procs from > within a stored procedure, and check the return codes to > determine if a > rollback needs to happen before processing is complete). > > So, any suggestions? Thanks in advance. > > Shawn Grover > > > ______________________________________________________________________ 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
