Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: TRIGGER code for an auto-incrementing field?
Phil.
First, you must create this trigger with the "FOR EACH ROW" clause.
Second, you can't use NEXTVAL directly - it must be done in a query.
So your code should be:
CREATE OR REPLACE TRIGGER "RESLIB"."NEWID" BEFORE INSERT ON
"RESLIB"."P_Well"
FOR EACH ROW
BEGIN
SELECT PW.NEXTVAL
INTO :new.ID
FROM DUAL;
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?
>
> ----------------------------------------------------------------------------
> Phil Scadden, Institute of Geological and Nuclear Sciences
> Work: PO Box 30368, Lower Hutt Home: 55 Buick St, Petone, Lower Hutt
> New Zealand
> ph +64 (04) 569-9059, fax (04) 569 5016 ph (04) 568-7190,
-- ==================================================== Oren Nakdimon Golden Screens Ltd. address: 17 Abba-Hillel st., Ramat-Gan 52522, ISRAEL email: oren_at_gsit.co.il tel: +972-3-7510836 fax: +972-3-7518221 ====================================================Received on Tue Oct 28 1997 - 00:00:00 CST