Re: Simulate sequence on WHEN-CREATE-RECORD

[Quoted] [Quoted] I think I got it sorted out, input welcome, I instead put the code in the [Quoted] PRE-INSERT trigger...

SELECT NVL(MAX(task_id),0)+1

    INTO :TASK_ID
    FROM task_list;

much less overhead,
Thanks anyhow,
--Aaron V

"Aaron V" <Tinkerist_at_hotmail.com> wrote in message news:kKedneF-f8sA1H-i4p2dnA_at_comcast.com...
> Hello All, db 9i, forms 6i
>
> I've just determined (it should have dawned on me the first school day, or
> someone should have set me right a long time ago), to Never, Ever have a
> primary Key be the Social Sec No., employee no or Anything the User can
Type
> or Input in ANY way. Dunce Hats aside now, I'm trying to find a good, or
> better than I've been doing, way to generate sequential numbers for the ID
> Field. I used a Sequence for the initial value
(:SEQUENCE.my_seq.NEXTVAL),
> but then I need a separate sequence for every table that requires a
primary
> 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,
> there would be gaps in the Sequence(I know it's a little thing, but it
still
> would bug me).
>
> The best I could come up was to create an additional Calculated item that
> keeps the Max value, then on a WHEN-CREATE-RECORD trigger, query the
db(for
> records not displayed) compare that to the Calculated Max, then add one to
> 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 - 03:17:57 CET