Re: Simulate sequence on WHEN-CREATE-RECORD
Date: Fri, 19 Dec 2003 16:39:25 -0800
Message-ID: <ltSdnYjyn5B2B36iRVn-sQ_at_comcast.com>
[Quoted] [Quoted] "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1071815572.674372_at_yasure...
> 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
> >
> > 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
>
> 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
> dialog box, validate the input, and then insert into the table.
>
> 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.
>
> 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)
>
Thanks Daniel,
[Quoted] your absolutely right about the multi-user environment, I'm still learning
[Quoted] :-), so I guess the only way to get a consistently increasing unique value
[Quoted] [Quoted] would be to use a Sequence. But, if I were to use a BEFORE INSERT trigger
[Quoted] in the database and had a Master-Detail Relationship on the form (which I
[Quoted] have), you'd have to commit, then re-query the master info before creating
any detail records. Or the detail records would have a null Foreign Key, So
[Quoted] I put the code in the Pre-Insert Trigger at the Block Level, and it seems to
[Quoted] be fine, although I may also put a DB trigger for input from other sources.
[Quoted] [Quoted] But I'm still not convinced about using a SSN or Employee No for the primary [Quoted] Key, what if one where to mistype the information, or provide inaccurate documents, or anything else and this isn't found out until this record resides in 20 odd other child tables. Whereas if the SSn was in only One [Quoted] table and an unrelated, generated unique value was used as the Primary Key, [Quoted] then this problem could not exist, pertinent information is only stored Once.
--Aaron Received on Sat Dec 20 2003 - 01:39:25 CET