Need help on the Oracle Sequence Number

From: Ken <klau318_at_gmail.com>
Date: 2 Dec 2004 14:29:49 -0800
Message-ID: <6f6287d6.0412021429.178417c_at_posting.google.com>

[Quoted] I got an Access database that need to be converted to Oracle 9i. Somehow the Trigger we created to simulate the "AUTO NUMBER" on Access could not create the sequence number as soon as the value has been inserted. The sequence number can only be created after we go to the second line. Please see the trigger below.

Is there anyway we could create a trigger that could create the sequence number as soon as we enter a value? It should be very similar to the "Auto Number" on Access, or "Identity Seed" on SQL Server.

---

1. sequence SNP.SECTION_ID_SQ:

CREATE SEQUENCE SNP.SECTION_ID_SQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    NOORDER
/

GRANT SELECT ON SNP.SECTION_ID_SQ TO "PUBLIC"
/

2. Trigger SNP.SNP001_T_I_GET_NEXT_SECTION_ID:

CREATE OR REPLACE TRIGGER SNP.SNP001_T_I_GET_NEXT_SECTION_ID BEFORE INSERT
ON SNP.SNP001_SECTION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (new.section_id IS NULL) BEGIN
   SELECT section_id_sq.nextval
   INTO :new.section_id
   FROM dual;
END; Received on Thu Dec 02 2004 - 23:29:49 CET