Auto insertion of sequence in tables 1

[myolp]

I've recently started using Oracle 8i and discovered that there is no "easy" way of making the primary key automatically increment its value (compared to IDENTITY in MSSQL or auto_increment in MySQL). The way to solve it would be to use a sequence and add a trigger for the table that would retrieve NEXTVAL from that sequence.

My question is: Are there any other ways to do this? I don't really like using trigger unless I really have to. It also seems like an awful waste for doing something that simple since most primary keys should be automatically incremented.

 

[THoey]

You don't really need a trigger, just use it in your INSERT statement:

INSERT INTO MyTable (Id, Name)
VALUES (MySequence.NextVal, "SMITH&quot

If I am misunderstanding, please repost...
Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Leon1977]

you can find the current value with curval:
select seq_scr.currval from dual

 

[listener22]

Note that depending upon your sequence buffer setting,
among others, that you may experience some gaps in
your sequence(s). The buffer gets some number of
sequences, say 10 or 20, but if they're not used in some
period of time, the remaining are tossed and the sequence
starts again at a higher number. I think the way it works
is that if you want no gaps, say for invoice numbers or
something, you turn off the buffering. Makes it slower,
but no gaps. Jim

oracle, vb

 

[myolp]

Ok, i guess you all misunderstood me. I'm well aware of the fact that you can use Sequencename.NEXTVAL at insertion, but I need it to be done automatically, like in all other RDBMS i've tried. Is it possible to solve without using triggers?

 

[bcouse]

There is no way to directly associate the sequence with your column, nor is there a perfect black-box way of solving your problem. One thing to consider...

If you need to get that primary key value for additional/subsequent processing, popping the nextval in a trigger or directly inside the insert statement is somewhat problematic - Now you have to re-select the record to get the value!@#$. Just depends on your requirements...

best-o-luck,
bcouse

 

[faheemrao]

Ok can I ask you that why you want the Primary Key to be Auto increment.?

Why cannot you just put the Seq.nextval in you insert statements.

If you really want to do this ,

try to set the default value of that column to seq.nextval.
I am not sure it going to work or not , but this is the Only solution I can think for Now.!!!



Faheem Rao

 

[ginodanimal]

If you are still stuck, try this link. It worked for me!

http://www.jlcomp.demon.co.uk/faq/autonumb.html

Good Luck - Gino

 

[carp]

Myolp -
The short answer to your question is "no".
You have two basic approaches:

1. Use a trigger that will provide the seq.NEXTVAL every time it fires.

2. Insert seq.NEXTVAL along with the rest of your column values. Now, having said that, you might want to consider writing a procedure that accepts all of the column values (except for your Primary Key)as arguments and includes the call to seq.NEXTVAL in there. But this is liable to be as attractive as setting up a trigger!