We appear to just be going around in circles on this one. If you have a query that selects the sequence next value then you do not need a CFTRANSACTION but you do have to remember to always select the next value since I'd assume this uses no trigger to handle it on any other inserts? What do you do for mass inserts from another table, guess just a little addition to the SQL and really no biggie on that one. Or you take the approach of never inserting anything manually into the "ID" column and just little a trigger/sequence handle it. That means using CFTRANSACTION so that after you insert it you do the SELECT MAX(ID) AS NEWID .... Since there seems to be no adverse reason to using CFTRANSACTION unless in some of the already noted situations which seem more out of the norm than the norm, then this method just to me seems like an easier approach.
On 7/12/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote: > > The two query approach secures the id from the sequence though. No other > thread, request, etc can get the same sequence value. > > Now, if you were to manually build and control a numeric sequence ala > SELECT > Max(id) + 1 as newid FROM tblname, then you would have to worry about this > issue and use a transaction or lock orsomething. But with Oracle's > sequence, > no need to worry. > > DK > > On 7/12/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: > > > > Yes, but isn't the point in doing the CFTRANSACTION in the way I am > > describing to ensure that you get the ID of what you just inserted in > > order > > to pass it back to the website? More than likely to pass it back with a > > URL > > for telling the user hey this is where you go to view status or to edit > > what > > you just put in our database. I thought the point in doing the > > CFTRANSACTION > > for this was not so that you can do a rollback on the sequence(which we > > all > > agree can not happen anyway) but was to ensure when things are being > > inserted by multiple people and around the same times that each person > > gets > > the ID that belongs to them. > > > > On 7/12/05, Douglas Knudsen > > > > ok. as I mentioned earlier in the this thread, a rollback does not > effect > > a > > sequence. So wrapping a transaction around these too queries is no > > different > > really then wrapping it around the single insert. > > > > DK > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211639 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
