Re: Row ID Auto Enumeration question.

Eugen Nyffeler <eugen.nyffeler_at_ubs.com> wrote in article <356AB0DB.3022064E_at_ubs.com>...
> Jeffrey T. Kempiners wrote:
> >
> > Hello all,
> > I'm attempting to add a column to an existing table, such that
the new
> > column will keep a unique, non-null integer containing the row number.
> >
> > I know when I return * from an sql prompt, I get a column called Row #,
but
> > I've not found a way to retrieve this into a usable form.
> >
> > I know this is not very difficult, I don't need the column to
> > auto-enumerate when a row is deleted, just to add a number when a new
row
> > is created.
> >
> > If anybody has any suggestions on how to store this auto-numbered value
> > into a field, (outside the realm of stored procedures) please let me
know.
> >
> > Your assistance is greatly appreciated,
> >
> > -Jeffrey T. Kempiners
>
> Have you tried to use a seqeunce in a after insert trigger on the
> specific table ??

Yes, my sequence is set ok, and here's the body of the trigger... BEGIN
UPDATE SIR SET ROW_NUMBER = TAB_SEQ.NEXTVAL; END;
where TAB_SEQ is the name of my table sequencer.

Here's the problem, every time I insert a new row, the entire table re-sets it's numbers. So I've got row 1, I insert another row, now they're called row 2 and row 3.
Add another, and I've got row 4,5,and 6. Add another, and it's 7,8,9, and 10.
So I need to add something in the trigger body that says only update the NEW record, none of the others. Any ideas on how to do this? Thanks very much
-jeff Received on Tue May 26 1998 - 16:52:51 CDT