Re: Catching Surrogate Key Value...

One method:

make use of the RETURNING clause:

==========OraDoc==============

Inserting Using Bind Variables Example The following example returns the values of the inserted rows into output bind variables :bnd1 and :bnd2:

INSERT INTO emp VALUES
(empseq.nextval, ’LEWIS’, ’CLARK’,7902, SYSDATE, 1200, NULL, 20) RETURNING sal*12, job INTO :bnd1, :bnd2;

========end OraDoc =====================

• insert the parent, thereby getting the new ID

-insert the childs

other method:

• have a package to encapsulate all NEXTVALs on sequences
• get a new ID from the package
• insert the parent
• insert the childs

On Mon, 20 May 2002 11:58:29 +0100, "Nick P" <nick_pope_at_nospamhotmail.com> wrote:

>What is the best method for catching the value of a surrogate key (auto
>increment numeric ID) when creating a 'parent' row in one table and 'child'
>rows in another table?
>
>I need to know the value of the surrogate key in the 'parent' row to
>populate the foreign key in the 'child' rows.
>
>E.g.
>
>I have entered some details on screen detailing a new contact organisation
>and a few contacts within that organisation. I hit 'Save' to populate the
>database with this information.
>
>The parent ORGANISATIONS row is created with an internally created numeric
>ID.
>
>I next need to create the CONTACTS rows. How do I get the ORGANISATIONS.ID
>value to put into the foreign key ORG_ID column? What is the best method?
>
>Thank you in advance...
>
>Nick
>
>

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon May 20 2002 - 06:27:44 CDT