Re: TRIGGER code for an auto-incrementing field?

From: Ken Nichols <knichols_at_mcsilo.ilo.dec.com>
Date: 1997/10/29
Message-ID: <34576140.161C@mcsilo.ilo.dec.com>#1/1

Hi Phil,

Here's one cut-and-pasted from my working code:

create trigger tib_collectiondetails before insert on CollectionDetails for each row
declare

    integrity_error exception;

    errno            integer;
    errmsg           char(200);

    v_CollectionID CollectionDetails.CollectionID%TYPE;

begin

• Get the next sequence value for the current table SELECT CollectionDetails_seq.nextval INTO v_CollectionID FROM dual;

    :new.CollectionID := v_CollectionID;

• Errors handling exception when integrity_error then raise_application_error(errno, errmsg); end; /

mdlcpgs_at_lhn.gns.cri.nz.nospam wrote:
>
> This has to be basic, basic but as beginner, I am having trouble.
>
> I want to implement an autoincrementing field in a table, and as it can
> updated via ODBC, I need to do this with a TRIGGER.
>
> I created a sequence and tried a trigger of:
> CREATE OR REPLACE TRIGGER "RESLIB"."NEWID" BEFORE INSERT ON
> "RESLIB"."P_Well"
> BEGIN
> :new.ID := PW.NEXTVAL;
> END;
>
> However I get "NEWID" fails on revalidation when running it.
>
> Where did I go wrong?
  Received on Wed Oct 29 1997 - 00:00:00 CST