By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,017 Members | 1,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,017 IT Pros & Developers. It's quick & easy.

Need help on the Oracle Sequence Number

P: n/a
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;
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.