Re: Autonumber Field in Oracle

Hi, Chris.
The script for your trigger must look like this :

CREATE OR REPLACE TRIGGER "DEVCCAPSON"."S_AUTONUMBER"    BEFORE INSERT ON "DEVCCAPSON"."PSFINDEX" FOR EACH ROW declare

   PK_Value NUMBER(11);
begin

• SELECT AUTONUMBER.NEXTVAL INTO PSFID FROM PSFINDEX; --It's wrong !! SELECT AUTONUMBER.NEXTVAL INTO PK_Value FROM DUAL; :new.PSFID := PK_Value; end;

Best Regards,
Maxim.
e-mail: maxim.krapivin_at_usa.net

capsoncc_at_nbnet.nb.ca wrote :
>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
Received on Wed Feb 03 1999 - 05:04:36 CST