As i see it, the benefit of this approach is that it will be more portable across different databases (as long as they understand transactions). The down side is that if you want this to work you will have to use <cftransaction isolation="serializable"> - "repeatable_read" won't cut it if another transaction has finished in the mean time. Unfortunately, some other products implement this level of isolation by locking the whole table - which means that only one thread at a time will be able to insert into this table.
My advice: oracle cost a lot of money, so get your money's worth: use oracle specific features when they make your life easier. It's what they're for. >-----Original Message----- Subject: retrieve ID from db insert >From: Aaron Rouse <[EMAIL PROTECTED]> >Date: Tue, 12 Jul 2005 11:10:05 -0500 >Thread: >http://www.houseoffusion.com/cf_lists/index.cfm/method=messages >&threadid=41103&forumid=4#211639 > >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. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211666 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
