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 > > > > > > > >
