Re: Autonumber Field in Oracle

I tried your recommendation but i get the following error:

ORA-04098: trigger 'S_AUTONUMBER' is invalid and failed re-validation.

Here is my table structure:
SQLWKS> desc psfindex

Column Name                    Null?    Type
------------------------------ -------- ----
PSFID                          NOT NULL NUMBER(11)
GMAXCONTRACTNUMBER                      VARCHAR2(11)
APPLICANTNAME                           VARCHAR2(60)
APPLICANTCODE                           VARCHAR2(6)
UPDATEDDATE                             DATE
UPDATEDUSER                             VARCHAR2(20)
GMAXDESCRIPTION                         VARCHAR2(60)

I have a primary key created on the PSFID field.This is the field that I want to have automatically incremented.

Here is the SQL statement for my trigger: CREATE OR REPLACE TRIGGER "DEVCCAPSON"."S_AUTONUMBER" BEFORE INSERT ON "DEVCCAPSON"."PSFINDEX"
begin
SELECT AUTONUMBER.NEXTVAL INTO PSFID FROM PSFINDEX; end;

AUTONUMBER Is the name of my sequence.

ANy help would be greatly appreciated since I am on a tight schedule.

Thanks
Chris Capson

In article <36B6ADA0.BAF44C86_at_Privat.Post.DE>,   GreMa_at_t-online.de (Matthias Gresz) wrote:
>
>
> Chris Capson schrieb:
> >
> > I am currently migrating a Microsft Access 97 application to Oracle. I have
> > created all of my tables but i can't figure out how to handle the autonumber
> > field from Microsoft access. This autonumber field should automatically
> > increment by one anytime anybody inserts a record into the table. I have
> > been reading about it and it sounds like Oracle has some sort of CREATE
> > SEQUENTIAL. I am note sure how to set it up so that anytime a record is
> > added that the primary key field will be incremented automatically. If
> > anybody has any recommendations or examples they would be greatly
> > appreciated.
> >
>
> You've got to use SEQUENCES and TRIGGERS:
>
> Create Sequence SEQ_DL_Honorar_ID;
>
> create or replace trigger tBI_DL_Honorar before INSERT on DL_Honorar for
> each row
> begin
> SELECT SEQ_DL_Honorare_ID.NEXTVAL INTO :new.HonorarNr FROM DUAL;
> end;
> /
>
> HTH
> Matthias
> --
> Matthias.Gresz_at_Privat.Post.DE
>
> Always log on the bright side of life.
> http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Feb 02 1999 - 11:05:10 CST