Re: Need help on the Oracle Sequence Number

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 02 Dec 2004 15:46:01 -0800
Message-ID: <1102031061.920968_at_yasure>


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_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Dec 03 2004 - 00:46:01 CET

Original text of this message