Re: "autonumber" field - how to?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 22 Nov 1999 16:56:53 +0800
Message-ID: <38390555.73ED@yahoo.com>

Bill Cohagan wrote:
>
> I'm an Oracle newbie, having worked previously in Access and SQL7. In both
> of these systems I am able to easily create an "autonumber" field; i.e., a
> field that is automatically assigned sequential integers as new records are
> created. I'm having difficulty figuring out how to do this in Oracle 8. (I'm
> actually using Personal Edition O8).
>
> I've discovered the Sequence object and that is clearly the mechanism to be
> used. I've also discovered that you cannot reference this object (or its
> NEXTVAL pseudocolumn) in the DEFAULT spec for a column. I've also attempted
> writing an INSERT trigger, but can't get it to compile. The statement:
>
> :new."ID Field" := table_SEQ.NEXTVAL
>
> causes a "trigger compiled with errors" message. When I then type SHOW
> ERRORS, I get "No Errors" !!! If I change the line to:
>
> :new."ID Field" := 99
>
> then it compiles fine.
>
> I would appreciate hearing from anyone who can help me solve this problem.
>
> TIA,
> Bill

replace with:

select seq_abc.nextval
into :new.field
from dual;

and SHOW ERRORS will work, you just need a little more info - you can use:

show errors trigger trigger_name

(and similarly with procs/funcs/packages)

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Nov 22 1999 - 02:56:53 CST