Re: Autonumber trigger

John Peterson wrote:

> 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

Any before insert trigger with an IF statement will do the job.

But please don't select from dual. Just put the code right into the insert statement such as:

INSERT INTO mytable
(myfield)
VALUES
(SEQ_TBL_C_C_SECCOLOR.NEXTVAL); The select from DUAL is inefficient.

Daniel A. Morgan Received on Wed May 16 2001 - 01:19:03 CDT