Re: Basic sequence question

On Oracle 8i you wouldn't have to, The tables don't need to each have a sequence. A single sequence for all will work. Each table has insert trigger assigning key from the sequence. The insert statement uses the RETURNING clause with the PK key column value to the caller. A surrogate PK is just key to ensure uniqueness.

If the insert fails for whatever reason or is rolled back. The sequence is still incremented and doesn't "remember" this case. So you still don't know where your are with a current value.
The RETURNING clause makes select seq.currval unnecessary. In fact with a single seq. The triggers should call a function/procedure which references the seq. Users don't have direct access to the seq at all.

hth
Dave Fowler

"Avery" <avery_at_aloha.net> wrote in message news:yijj6.21448$_D.3091578_at_typhoon.we.rr.com...
> Yes create one for each.
>
> <u366699218_at_spawnkill.ip-mobilphone.net> wrote in message
> news:l.982311650.1485961914_at_c1026384-a.crvlls1.or.home.com...
> > I'm an Oracle sequence novice. I have several tables with primary
> > key IDs that need to be auto-incremented, and sequences appear to be
> > the best way to do that. My question is, do I need to create a separate
> > sequence object for each table? I would think that I do, so that the
> > objects can 'remember' their current value. Am I correctly
> > interpreting their function?
> >
> > Thanks in advance...
> >
> >
> >
> >
> > --
> > Sent by g from yahoo subpart of com
> > This is a spam protected message. Please answer with reference header.
> > Posted via http://www.usenet-replayer.com/cgi/content/new
>
>
>
Received on Sun Feb 18 2001 - 21:03:00 CST