Re: Simulate sequence on WHEN-CREATE-RECORD

Aaron V wrote:
> I think I got it sorted out, input welcome, I instead put the code in the
> 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

>>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
>>Field.  I used a Sequence for the initial value

>>but then I need a separate sequence for every table that requires a

>>key,  if I export data to another box I'll have to modify or recreate the
[Quoted] [Quoted] >>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

[Quoted] >>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

>>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
[Quoted] >>  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

[Quoted] I think you were doing better before you answered your own question. There is nothing wrong with using social security numbers of primary keys if, in a given situation, they are the actual value that uniquely identifies a row of data. The solutions are many but one is to pop up a [Quoted] dialog box, validate the input, and then insert into the table.

[Quoted] But if you are going to use a surrogate key I would still go with a sequence in the database and use a BEFORE INSERT trigger in the database to generate the number.

[Quoted] [Quoted] Your home-cooked solution will absolutely not work in a multi-user environment. By definition ... two people, heck every connected user, could get the same number.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)