Simulate sequence on WHEN-CREATE-RECORD

I've just determined (it should have dawned on me the first school day, or [Quoted] [Quoted] someone should have set me right a long time ago), to Never, Ever have a [Quoted] primary Key be the Social Sec No., employee no or Anything the User can Type [Quoted] or Input in ANY way. Dunce Hats aside now, I'm trying to find a good, or [Quoted] better than I've been doing, way to generate sequential numbers for the ID [Quoted] [Quoted] Field. I used a Sequence for the initial value (:SEQUENCE.my_seq.NEXTVAL), [Quoted] but then I need a separate sequence for every table that requires a primary [Quoted] key, if I export data to another box I'll have to modify or recreate the Sequence, and if the user where to press Down-Up-Down at the last record, [Quoted] there would be gaps in the Sequence(I know it's a little thing, but it still [Quoted] would bug me).

The best I could come up was to create an additional Calculated item that [Quoted] keeps the Max value, then on a WHEN-CREATE-RECORD trigger, query the db(for [Quoted] records not displayed) compare that to the Calculated Max, then add one to [Quoted] the greater value for the ID number...

SELECT GREATEST(NVL(MAX(task_id),0),NVL(:MAX_ID,0))+1   INTO :TASK_ID
  FROM task_list;

Is there any way to do this in a simpler, more efficient way??

Happy Holidays, and Thanks for any Advice, --Aaron V Received on Fri Dec 19 2003 - 01:40:15 CET