On 2 Dec 2004, klau318_at_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
Received on Fri Dec 03 2004 - 08:16:20 CST
