Re: Simulate sequence on WHEN-CREATE-RECORD

From: Aaron V <Tinkerist_at_hotmail.com>
Date: Mon, 22 Dec 2003 15:12:41 -0800
Message-ID: <AeqdncBxw7WF5nqiRVn-hA_at_comcast.com>

"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0312221016.28b3c621_at_posting.google.com... > "Aaron V" <Tinkerist_at_hotmail.com> wrote in message news:<ltSdnYjyn5B2B36iRVn-sQ_at_comcast.com>...
> > "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,
> > your absolutely right about the multi-user environment, I'm still
learning
> > :-), so I guess the only way to get a consistently increasing unique
value
> > would be to use a Sequence. But, if I were to use a BEFORE INSERT
trigger
> > in the database and had a Master-Detail Relationship on the form (which
I
> > 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
> > I put the code in the Pre-Insert Trigger at the Block Level, and it
seems to
> > be fine, although I may also put a DB trigger for input from other
sources.

> >
>
> You might still have problems in multiuser environment. Until data

commited,
> you can't see it from other sessions. Sequences is the only way to generate

> artificial primary keys.
>

> > But I'm still not convinced about using a SSN or Employee No for the
primary
> > 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
> > table and an unrelated, generated unique value was used as the Primary
Key,
> > then this problem could not exist, pertinent information is only stored
> > Once.

> >
>
> The problem of artificial PK vs meaningful PK is much discussed and

general

> solution is still not clear. Once you have artificial PK, you broke 3rd
> NF, which is not good. If you have meaningful PK, you can't change it
> if you need to, which might be bad. Real world always screws up good
> theory, darn it.
>

> > --Aaron

Thanks all for the advice,
I have a solution that works for this situation, although it may not be perfect in theory. I'd rather have something that's easier to update the data, than have to write some sort of Cascade Update Triggers for every PK table.
By the way, I was toying with the idea of having one primary Key Sequence to generate all PKs, would there be any advantage to having one Sequence for every PK table? Because, if I were to move data to another box and continued to insert records, I'd only have to alter one Sequence.

Thanks,
--Aaron V Received on Tue Dec 23 2003 - 00:12:41 CET