Re: Row ID Auto Enumeration question.

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 Received on Thu May 28 1998 - 09:23:38 CDT