sequence increment based on MAX()

[yorge]

Hi

I need some help. Given a table "mytable" whose primary key is "myid" and a sequence assigned to this field for table "mytable_seq", If MAX(myid) is 100 and mytable_seq.currval is 50, I want to reset the value of mytable_seq to be 100...so that the next time a mytable_seq.nextval is called, it will be 101 (increment by 1 each time)...and not 51. Since this scenario is also happening on other tables as well, I wanted to have this as a procedure that perhaps will take as parameters the tablename, seqname and table field that will use the sequence (most often the primarykey of the table).

Thanks in advance,
yorge

 

[Dagon]

The best solution would be stop all this nonsense by creating a before insert trigger which sets the primary key from the sequence. Then you are guaranteed that the primary key will be populated from the sequence. If another process passes a value for the ID which is higher than the current sequence number, it wouldn't matter because the value gets overwritten in the trigger.

Failing that, you'd probably have to do something very complicated with statement level triggers. You can't use row-level triggers directly because you wouldn't be allowed to read the table to find what the highest value of the primary key currently is.

You'd have to use the row level trigger store all the rowids of the newly created rows in a PL/SQL table defined in a package. You could then use an after insert trigger to check the Ids of the new rows are lower than the highest primary key value. If so, advance the sequence by calling sequence.nextval in a loop and update the rows to have new values.

It would be horrible to code and might not work very well in a high transaction environment.


For Oracle-related work, contact me through Linked-In.