Re: Catching Surrogate Key Value...

From: Mark D Powell <mark.powell_at_eds.com>
Date: 20 May 2002 06:38:31 -0700
Message-ID: <178d2795.0205200538.57b9b18@posting.google.com>

"Nick P" <nick_pope_at_nospamhotmail.com> wrote in message news:<uehll0ho3nu616_at_corp.supernews.com>...
> 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

Nick, Look at the returning clause; it allows you to return any value just inserted via your insert into a bind variable.

Or select the sequence value; move it into your parent insert key field, and then carry it over to your child rows.

UT1> set echo on
UT1> set serveroutput on
UT1> declare
  2  --
  3  v_ctr       number := 0 ;

  4 --
  5 begin
  6 insert into marktest values
('test',TEMP_PRICE_HEADER_SEQ.nextval,sysdate)   7 returning fld2 into v_ctr ;
  8 dbms_output.put_line('v_ctr = '||v_ctr);   9 end;
 10 /
v_ctr = 2632

PL/SQL procedure successfully completed.

UT1> select * from marktest where fld1 = 'test'   2 /

FLD1 FLD2 FLD3
---------- ---------- ---------
test 2632 20-MAY-02

HTH -- Mark D Powell -- Received on Mon May 20 2002 - 08:38:31 CDT