"autonumber" field - how to?

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 Received on Mon Nov 22 1999 - 10:38:40 CST