Re: Autonumber trigger

Hello, Metra!

Do you know if the trigger can be written to handle the case where the INSERT explicitly specifies a value for the column that is being set by the SEQUENCE? By that I mean you've written a nice snippet of code that would allow you to run the following scenario:

create table Test
(
TestId NUMBER PRIMARY KEY,
Field VARCHAR2(8) NOT NULL
);

(Assuming a corresponding SEQUENCE/TRIGGER combination as illustrated by Metra.)

We could then run something like:

insert into Test(Field) values ('Test');

However, if we tried:

insert into Test values (100, 'Test');

It would use the NEXTVAL sequence value and not the specified 100.

I was wondering if you knew of a way to make this work. Furthermore, what would happen when the sequence hit a number that already existed? (Maybe loop until a valid value is used?) I'd welcome your thoughts on the matter.

Thanks!

John Peterson

"metra christofferson" <metra.christofferson_at_honeywell.com> wrote in message news:3AFFEF63.68F3A7F1_at_honeywell.com...
> Dmitry Lesov wrote:
> >
> > I need to to write a trigger that duplicates Autonumber in MS Access -
> > Id Field gets nextval from the sequence upon insert. But I don't know
> > how to write a trigger that does that. Appreciate any help.
> > Dmitry
>
> You need both a sequence and a trigger. Here's an example of each.
>
> CREATE SEQUENCE "MCHRISTO"."SEQ_TBL_C_C_SECCOLOR"
> INCREMENT BY 1
> START WITH 1
> MAXVALUE 999999999
> MINVALUE 1
> CYCLE
> CACHE 20
> ORDER;
>
>
>
> CREATE OR REPLACE TRIGGER "MCHRISTO".TRG_SEQ_TBL_C_C_SECCOLOR
> BEFORE INSERT ON "MCHRISTO"."TBL_C_C_SECCOLOR" FOR EACH ROW
> BEGIN
> SELECT
> SEQ_TBL_C_C_SECCOLOR.NEXTVAL
> INTO :NEW.ID
> FROM
> DUAL;
> END;
>
> Hope this is useful,
>
> Metra
Received on Tue May 15 2001 - 22:41:05 CDT