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;

[Trevor Best]

Ken wrote:

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.

No. You'd get the same behavior in SQL Server too, you might appreciate
the fact that the server cannot generate a number until you ask it to,
unless you save the record after entering the first bit of data (not
practical if you have required columns) then the first time you contact
the server about that record is when you've finished with it. Do what
everyone else does and re-design your front end to cope. You shouldn't
be *that* interested in the number that's been generated anyway.

Some groups dropped, FFS Ken, c.d.o.marketplace? You after a job?

--
This sig left intentionally blank

[Galen Boyer]

On 2 Dec 2004, kl*****@gmail.com 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, the best you can do is put section_id_sq.nextval within your
insert statement.

--
Galen Boyer