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