Aaron, This is another good solution.
Thanks, Matthieu -----Original Message----- From: Aaron Rouse [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 2:08 PM To: CF-Talk Subject: Re: new next primary key value in Oracle You could just run the insert on one table, then select out that new ID then use it for the other inserts. Something like: <cftransaction ...> <cfquery ...> INSERT INTO BLAH (COLA) VALUES ('YES')</cfquery> <cfquery ....> SELECT MAX(ID) AS NEWID FROM BLAH </cfquery> <cfquery ...> INSERT INTO BLAH2 (COLA, COLB) VALUES ('NO', #QueryName.NewID#) </cftransaction> With that I assume a trigger is done to get the new value out of the sequence and it puts it into Blah.ID during the insert. On 11/3/05, Ian Skinner <[EMAIL PROTECTED]> wrote: > > Hi, everyone. I have been doing something for years that works, but > feels darned silly. I have a table called WIDGETS. I have a sequence > set to increment by 1 up to some ridiculous number called > WIDGETS_PKSEQ. When I add a new row to WIDGETS, I put > WIDGETS_PKSEQ.NEXTVAL into WIDGETS.pkWIDG, the primary key for the > table. Often, immediately after creating a new row in WIDGETS, I want > to create a row in another table that references that new row in > WIDGETS: for example, ORDERS.fkORD_pkWIDG refers to WIDGETS.pkWIDG. > But to do that, I need to get that new WIDGETS.pkWIDG value, which has > never actually appeared on the ColdFusion side of the transaction; > it's all in Oracle. So, I do this ridiculous thing where I generate a > random (and safely unique value), write it to some row in WIDGETS on > the new row creation, look it up to get the new pk, then overwrite > that row with the appropriate value it should have had in the first > place, and then continue, now that ColdFusion has the new pk value > known. There's got to be a better way! I know I am missing something > easy. > > Any thoughts? > > Matthieu > > When I have run into this, having to put a incremental key value into > several tables, what I have done is select the WIDGETS_PKSEQ.NEXTVAL > and then use that value in all my inserts. Somewhat like this off the > top of my head example. > > <cfquery name="nextKeyVal" ...> > SELECT > WIDGETS_PKSEQ.NEXTVAL AS KEY > FROM dual /*I believe this is the proper 'table'*/ > </cfquery> > > Then I can refer to nextKeyVal.Key in all my inserts. I wrap this all > up in a <cftransaction> block so that if a problem occurs I hopefully > don't end up with 1/2 the data saved. > > -------------- > Ian Skinner > Web Programmer > BloodSource > www.BloodSource.org <http://www.BloodSource.org> > Sacramento, CA > > "C code. C code run. Run code run. Please!" > - Cynthia Dunning > > Confidentiality Notice: This message including any attachments is for > the sole use of the intended > recipient(s) and may contain confidential and privileged information. > Any unauthorized review, use, disclosure or distribution is > prohibited. If you are not the intended recipient, please contact the > sender and delete any copies of this message. > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:223063 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
