Need help on the Oracle Sequence Number

[Ken]

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;

[Daniel Morgan]

Ken wrote:
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;

No. And please, in the future, when you don't know which group
to post to try reading the charter and thinking about the
group names rather than rudely cross-posting to every group
you can spell.

Thanks.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)