Re: TRIGGER code for an auto-incrementing field?

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1997/10/28
Message-ID: <63422a$3sv$1@news00.btx.dtag.de>#1/1

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?
>

One possibility is:

drop trigger tI_Anrufe;
create trigger tI_Anrufe before INSERT on Anrufe for each row declare Cursor C1 is

                Select Max(ID) as Maximum from Anrufe; begin
  FOR PROVV_REC IN C1 LOOP
    :new.ID := PROVV_REC.Maximum + 1;
  END LOOP;
end;
/  

The other is using the oracle db-object SEQUQENCE, which will generate unique keys:

Drop Sequence SEQ_TELEFONNUMMER_ID;
create Sequence SEQ_TELEFONNUMMER_ID;

drop trigger tI_Telefonnummer;
create trigger tI_Telefonnummer before INSERT on TELEFONNUMMERN for each row
declare NeueNummer number;
begin

	Select SEQ_TELEFONNUMMER_ID.NEXTVAL into NeueNummer from dual;	
	:new.ID  := NeueNummer ; 

end;
/

-- 
Regards

M.Gresz    :-)

Received on Tue Oct 28 1997 - 00:00:00 CST