Of course, another reason to use a trigger is so that PKs are correctly generated _regardless_ of the application that's doing the inserting.
--- Rachel Carmichael <[EMAIL PROTECTED]> wrote: > It was a compromise... since they had already written their code, I > put > in the triggers so that it was transparent to them that the "key" > they > were generating was not being used. > > I had to give them something, since I was really trying hard NOT to > say > "I told you so!" > > > --- Yong Huang <[EMAIL PROTECTED]> wrote: > > Rachel, > > > > That's a good case to remember. Java programmers (or architects) > > sometimes miss > > those little things. > > > > I would ask why you used triggers to populate the PK field instead > of > > saying > > INSERT ... MYSEQUENCE.NEXT_VAL in the code, or even INSERT ... > SELECT > > ROWNUM > > (or ROWNUM+somefixedvalue). Wouldn't these perform better? > > > > Yong Huang > > > > --- Rachel Carmichael <[EMAIL PROTECTED]> wrote: > > > At one site I worked at, the programmers insisted on using Java > > > milliseconds as the primary key -- so that they wouldn't have to > > hit > > > the database twice (once to get the sequence number, once to > insert > > the > > > row). They swore up, down and six ways from Sunday that there > could > > > never, ever, EVER be a collision. > > > > > > After we had collisions in development, we switched to sequences > > (one > > > per table), with a trigger to populate the field on insert so > that > > they > > > wouldn't have to make the second round-trip. > > > > > > > > > --- Jonathan Gennick <[EMAIL PROTECTED]> wrote: > > > > The recent article that mentioned sequences got me to > > > > thinking. I might pitch a more detailed article on sequences > > > > to Builder.com. But a more interesting article might be one > > > > that explored various ways to automatically generate primary > > > > keys. So, in the name of research, let me throw out the > > > > following questions: > > > > > > > > What mechanisms have you used to generate primary keys? > > > > Which ones worked well, and why? Which mechanisms worked > > > > poorly? > > > > > > > > I've run up against the following approaches: > > > > > > > > * Hit a table that keeps a counter. This is the "roll your > > > > own sequence method". The one time I recall encountering > > > > this approach, I helped convert it over to using stored > > > > sequences. This was because of concurrency problems: with > > > > careful timing, two users could end up with the same ID > > > > number for different records. Is there ever a case when this > > > > roll-your-own approach makes sense, and is workable? > > > > > > > > * Stored sequences. I worked on one app that used a separate > > > > sequence for each automatically generated primary key. I > > > > worked on another app, a smaller one, that used the same > > > > sequence for more than one table. The only issue that I > > > > recall is that sometimes numbers would be skipped. But end > > > > users really didn't care, or even notice. > > > > > > > > * The SYS_GUID approach. I've never used SYS_GUID as a > > > > primary key generator. I wonder, was that Oracle's > > > > motivation for creating the function? Has anyone used it for > > > > primary keys in a production app? What's the real reason > > > > Oracle created this function? > > > > > > > > * Similar to SYS_GUID, I once worked on an obituary-tracking > > > > application that built up a primary key from, as best I can > > > > recall now: date of death, part of surname, part of first > > > > name, and a sequence number used only to resolve collisions, > > > > of which there were few. The approached worked well, > > > > actually, because whatever fields we munged together to > > > > generate a primary key gave us a unique key the vast > > > > majority of the time. > > > > > > > > The SYS_GUID approach is interesting, but if you need an ID > > > > number that users will see, and that users might type in > > > > themselves (e.g. social security number), is SYS_GUID really > > > > all that viable? > > > > > > > > Best regards, > > > > > > > > Jonathan Gennick --- Brighten the corner where you are > > > > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > > > > > > > Join the Oracle-article list and receive one > > > > article on Oracle technologies per month by > > > > email. To join, visit > > > > http://four.pairlist.net/mailman/listinfo/oracle-article, > > > > or send email to [EMAIL PROTECTED] and > > > > include the word "subscribe" in either the subject or body. > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: Jonathan Gennick > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services -- 858-538-5051 > > http://www.fatcity.com > > > > San Diego, California -- Mailing list and web hosting > > services > > > > > > > --------------------------------------------------------------------- > > > > To REMOVE yourself from this mailing list, send an E-Mail > message > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and > > in > > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > > (or the name of mailing list you want to be removed from). You > > may > > > > also send the HELP command for other information (like > > subscribing). > > > > > > > > > __________________________________ > > > Do you Yahoo!? > > > Protect your identity with Yahoo! Mail AddressGuard > > > http://antispam.yahoo.com/whatsnewfree > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Rachel Carmichael > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > > > San Diego, California -- Mailing list and web hosting > > services > > > > > > --------------------------------------------------------------------- > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > > subscribing). > > > > > > __________________________________ > > Do you Yahoo!? > > Protect your identity with Yahoo! Mail AddressGuard > > http://antispam.yahoo.com/whatsnewfree > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Yong Huang > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting > services > > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > __________________________________ > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > http://antispam.yahoo.com/whatsnewfree > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). === message truncated === ===== Paul Baumgartel Transcentive, Inc. www.transcentive.com __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
