Re: Retrieving (Autonumber)identity field after using trigger
Date: Thu, 24 Oct 2002 15:43:52 -0700
Message-ID: <ap9t2q$l4j$1_at_spiney.sierra.com>
procedure ...
my_num number;
begin
insert into my_table values (.....) returning my_field into my_num;
end;
"mike" <erniegerdie_at_yahoo.co.uk> wrote in message
news:5739d393.0210230244.36e81cf3_at_posting.google.com...
> Hi,
>
> I am generating an autonumber using sequences and triggers as follows
> (belows example was taken from http://www.oracle-base.com) :
>
> CREATE TABLE departments (
> ID NUMBER(10) NOT NULL,
> DESCRIPTION VARCHAR2(50) NOT NULL);
>
> ALTER TABLE departments ADD (
> CONSTRAINT dept_pk PRIMARY KEY (ID));
>
> CREATE OR REPLACE TRIGGER dept_bir
> BEFORE INSERT ON departments
> REFERENCING OLD AS old NEW AS new
> FOR EACH ROW
> BEGIN
> IF :new.id IS NULL THEN
> SELECT dept_seq.NEXTVAL
> INTO :new.id
> FROM dual;
> END IF;
> END;
> /
>
> To insert:
> SQL> INSERT INTO departments (description)
> 2 VALUES ('Development');
>
> My question is, how do I now retrieve the autonumber (Identity field)
> which I have just created ?
>
> Any help would be much appreciated
> mike
Received on Fri Oct 25 2002 - 00:43:52 CEST