Hi All, I agree with Mike. I don't even let them know the autonumber PK exists. If they need to see a consecutive number of some kind, create it from a table that shows the last number used. Then update the table after they save the record. If they don't save, you don't update. After a deletion you would have to update all of the consecutive numbers. I'm not sure what good this does except as a reference to where they are in the table.
Dave Billing Tall Tree Business Solutions ----- Original Message ----- From: "MikeB" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Tuesday, July 15, 2003 8:23 PM Subject: [RBASE-L] - Re: An autonumber trigger? > > IMHO, Autonumbers should not be used for any "real world" values. Values that have any significance > to the end user. If you have to have a number that must be sequential, maintain a value independent > of the autonumber in a one row table and lock it when you intend to grab it, increment it, use it > (or discard as the case may be) and then update the one row table, then unlock it for general > access. > > > > ----- Original Message ----- > From: "David M. Blocker" <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> > Sent: Tuesday, July 15, 2003 10:31 AM > Subject: [RBASE-L] - Re: An autonumber trigger? > > > > Dennis > > > > Good point about the Multi user issue. > > > > What about: > > > > 1. Column is NOT autonumbered > > 2. Before bringing up the screen, set variable to a random number >= > > 999,999,900 > > 3. Check for any rows in master / detail tables with this variable. > > 4. If are, redo steps 2 and 3 until unique > > 5. Set column on form to this variable > > 6. Trigger sets column to the next number. > > > > David Blocker > > > > ----- Original Message ----- > > From: "Dennis McGrath" <[EMAIL PROTECTED]> > > To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> > > Sent: Tuesday, July 15, 2003 9:42 AM > > Subject: [RBASE-L] - Re: An autonumber trigger? > > > > > > > Karen is seeing a long standing behavior. When you add a row to an > > > autonumbered table, the autonumber is incremented. If you decide not to > > > save the row, that number will be lost. obviously, if you later delete > > > a row, that number is also lost. This is perfectly logical, > > > understanding the alternatives. > > > > > > I tried the trigger idea. With a primary key, the insert triggers fire > > > after the row is saved. But if the id column is null, the insert never > > > succeeds, so the trigger does not fire. One could make the default > > > value of the column zero, but I know for a fact that in a heavy traffic > > > situation, some inserts will fail due to 2 or more users trying to > > > insert rows in the table simultaneously. > > > > > > I'm wondering if there is a way to have a trigger detect when a row is > > > created but never saved. If this were possible, one could save the > > > lost numbers in a table, and assign one in an insert trigger, if one > > > were available. Obviously, the new number which is now discarded would > > > have to be added to the cache table. Because these numbers are always > > > previously assigned autonumbers, they will never be larger than the > > > next autonumber value. > > > > > > Dennis McGrath > > > > > > > > > I think the primary key needs the autonumber, but when the insert > > > trigger is fired > > > > > > > > > Dennis McGrath > > > > > > > > > > > > > > > > > > --- Bernard Lis <[EMAIL PROTECTED]> wrote: > > > > What's a missing autonumber. If the field is autonumbered it should > > > > always get a number. Could it be that it appears numbers are > > > > skipped? > > > > That could happen if someone enters a row, then deletes it. > > > > If this is the case and you don't want any skipped numbers you could > > > > re-autonum before each entry. > > > > sel max(col_name) into maxno from table > > > > autonum col_name in table using (maxno+1) 1 nonum > > > > > > > > Bernie Lis > > > > > > > > > > > > ----- Original Message ----- > > > > From: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > > To: RBASE-L Mailing List <mailto:[EMAIL PROTECTED]> > > > > Sent: Monday, July 14, 2003 10:18 AM > > > > Subject: [RBASE-L] - An autonumber trigger? > > > > > > > > > > > > Has anyone done this? A client has decided they don't like > > > > the missing autonumbers. I have code to make sure a unique > > > > number is assigned -- that's not a problem. But I was > > > > wondering if there's a way to do it with a trigger, either > > > > a before or an after trigger. > > > > > > > > The problem is that the column is a primary key. That means > > > > it cannot be null. I was thinking of an 'after' trigger > > > > where the row is added, and then afterwards the row is > > > > updated with the correct number. But it couldn't be added > > > > with a null in the column because the PK would be violated. > > > > I could let it assign an autonumber, then do an update with > > > > the 'correct' number but I would run the risk that the update > > > > would be to a number higher than the autonumber and somewhere > > > > down the line they wouldn't be able to add a new record > > > > with the same autonumber (sorry, do you get that?) > > > > > > > > I'm trying to avoid putting code everywhere that they might > > > > be adding a row into this table, plus I know they put rows > > > > in from the r> prompt. > > > > > > > > > > > > Karen > > > > > > > > > > > > > > > > > > > > > > >
