Re: Need help on the Oracle Sequence Number

"Ken" <klau318_at_gmail.com> wrote in message news:6f6287d6.0412021429.178417c_at_posting.google.com...
| 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;
First, don't cross-post to all the c.d.o groups -- it gets the curmudgeons' hackles up real quick, and is absolutely unnecessary

Second, MS-Access is a client-based data-management program -- even if the MDB file resides on a network server, the work is being done on your workstation by the MS Access program running there

Oracle, on the other hand, is an RDBMS that runs on a server -- so it cannot do anything on your client-side program. It does not handle client-side functionality, so it cannot respond to data-entry events like MS-Access (nor can any other RDBMS) until the client-side program sends it a request, which is typically a SQL or PL/SQL statement

Triggers fire in response to DML statements (INSERT, UPDATE, DELETE -- INSERT in the case you posted) so the code is not executed until some program issues the appropriate SQL statement -- and the database has no way of communicating with the client application other than returning a status code (or optionally populating client side variables, but never initiating screen updates).

If you want to simulate something that the UI functionality of Access is providing, and have the Oracle database participate, you'll need your UI to send an appropriate request to the database after capturing the appropriate event.

Hope this gets you started.

++ mcs Received on Thu Dec 02 2004 - 16:45:23 CST