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

Auto Number or Identity Seed on Oracle Database

P: n/a
Ken
Need help on the Auto Number or Identity Seed on the Oracle Database

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;
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Ken wrote:
[snip]

Already answered in c.d.m.a and a shedload of oracle groups, why are you
asking an Access/Oracle question in a SQL Server group? You're going to
annoy Celko in a minute <g>

--
This sig left intentionally blank
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.