Re: Row ID Auto Enumeration question.

Eugen Nyffeler wrote in message <356D736A.89D76B_at_ubs.com>...
>Jeffrey T. Kempiners wrote:
>
><snip>
>
>> 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
>
>Sorry for the delay, but the server was very slooow and i got the
>message just now.
>
>For me it seems you have a trigger for the complete table and not for
>each
>changed row.
>Try (it's not tested !):
>create or replace trigger 1
>after insert on your_table
>for each row
>begin
> update sir set row_number = TAB_SEQ.NEXTVAL;
>end;
>
>If this still changes all rows, then try it with a where clause (e.g.
>where row_number
>is null (when you have no value inserted for the row_number))
>
>HtH
>eugen

Sorry for the extra bandwidth here, but I felt it necessary to include the previous posts. I have a question for the original poster... Look at your update statement, you aren't qualifying it to update a specific row, (no where clause) so it's updating ALL of the rows. From reading the snip of original post you don't want that, but rather the next sequential # for your row_number field?? I think you need we need a clarification what you really want this to do. Received on Thu May 28 1998 - 09:51:20 CDT