Re: Retrieving (Autonumber)identity field after using trigger

You can always select the value into your own variable and then update the field you want with that value.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:x8yt9.49969$Hj7.21089_at_rwcrnsc53...
> Look in the developer guide for returning.
> Jim
> "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 Thu Oct 24 2002 - 06:10:14 CEST