> 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;