Use DUAL. Step 1 Select WIDGETS_PKSEQ.NEXTVAL from dual
Step 2 Insert into WIDGETS using WIDGETS_PKSEQ.NEXTVAL Step 3 Insert into child table of WIDGETS using value from step 1. -----Original Message----- From: Cornillon, Matthieu (Consultant) [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 10:52 AM To: CF-Talk Subject: OT: new next primary key value in Oracle 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:223116 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
