[RBASE-L] - Re: An autonumber trigger?

[Alastair Burr]

Karen,   Just thinking out loud so this might be rubbish...   Could you create a [common] [temp] table that is auto-numbered, select the idnum from that table then if, and only if, the row is acceptable add it to the real table and select the current max idnum and add 1 to it?   Alternatively, if you want to fill in blank numbers either keep a table with them in or use a cursor to find the first or last blank?   One solution that I have used is to add rows to a table each year/month/day/whatever and flag them as editable then edit the earliest row that is available rather than adding rows. Horses for courses but it might help. (My app actually checks that there are enough rows available and automatically adds more if need be. I do so this so that I need only an edit form and not an entry one as well.)   Regards, Alastair.     ----- Original Message ----- From: David M. Blocker To: RBASE-L Mailing List Sent: Tuesday, July 15, 2003 2:37 PM Subject: [RBASE-L] - Re: An autonumber trigger? Karen   Could you autoassign the value 0 or a very high number like 999999999 and then use your after trigger to update the row?   David   ----- Original Message ----- From: [EMAIL PROTECTED] To: RBASE-L Mailing List 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