Auto Number or Identity Seed on Oracle Database

[Ken]

Need help on the Auto Number or Identity Seed on the Oracle Database

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;

[Trevor Best]

Ken wrote:
[snip]

Already answered in c.d.m.a and a shedload of oracle groups, why are you
asking an Access/Oracle question in a SQL Server group? You're going to
annoy Celko in a minute <g>

--
This sig left intentionally blank