Re: autonumber with trigger and/or sequence

<whitsmore> wrote in message
news:k461fu09n98dfirvq5viph7h23tbei9lru_at_4ax.com...
> I'm stumped. I'm new to oracle and wanted to do something that MS
> access does. Access will generate a primary key with an autonumber.
> So, I've read past threads on google and I'm just missing something.
> To make it simplier for someone to answer, I will recreate
> step-by-step. Maybe someone can pick apart my problem.
>
> I created a table called TEST in AMY schema and PK being primary key:
>
> CREATE TABLE "AMY"."TEST" ("PK" NUMBER(7) NOT NULL, "PART"
> VARCHAR2(10) NOT NULL, "DESC" VARCHAR2(10) NOT NULL, PRIMARY
> KEY("PK"))
>
> I created sequence called seq_pk:
>
> Create sequence s_pk
> Increment by 1
> start with 1
> maxvalue 9999999
> nocache
> nocycle;
>
> I created trigger called tr_pk:
>
> create of replace trigger amy.tr_pk before insert on
> amy.test for each row begin
> select seq_pk.nextval into :new.pk from test;
> end;
>
> So, here's my problem. When I use MS access as a frontend (linked to
> Oracle table called TEST, I get the following error:
>
> The field is too small to accept the amount of data you attempted to
> add. Try inserting or pasting less data.
>
> When I use the the OEM, right click on TEST, table editor, I enter in
> the value for part and desc and leave pk blank. When I click on
> apply, I get an error message of:
>
> ORA-01403: no data found
> ORA-06512: at "amy.TR_PK", line 2
> ORa-04088: error during execution of trigger'amy.tr_pk'
>
> So, how do I get the PK field to use an autonumber. TIA

 create of replace trigger amy.tr_pk
 before insert on amy.test for each row
begin
 select seq_pk.nextval
into :new.pk
from dual; -- change of table here, dual always contains *one* row, and is used in *ALL* examples
 end;

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address