That is a good point about flagging and not deleting. In my case it is a calendar application and I use a status code for entries that are moved, canceled or confirmed and therefore having another status of Deleted might make better sense with better tracking. I otherwise record deletions in an audit log table so there is a record of who/when/what, etc.
On Wed, Dec 6, 2017 at 1:18 PM, Bruce Chitiea <rby...@safesectors.com> wrote: > That is a point, although if deleting records is a common practice, one > might do better by disabling AUTONUM entirely, letting cascading deletes do > their thing without concern for whatever the resulting ( (MAX(KeyID)) + 1) > value is, and generating ( (MAX(KeyID)) + 1) values with the code sample > below. > > BTW A hat tip to R:azzak for providing that code trick some time back. > Works great for "garbage in", which is my primary, if not exclusive, focus. > > For "garbage be gone", I made the decision some time ago to design tables > (in which record deletion MIGHT otherwise be a valid consideration ) with a > "mark deleted" column, leaving data (and all relational keys) in place. A > personal choice, based on the single-user, "one-off", forensic nature of my > databases and an admitted squeamishness about cascading critical history > into oblivion. Being of relatively small size ( <1M records ), given the > power of R:BASE today, table bloat is hardly a consideration for me. One > other "iron rule" is that key column values have NO meaning beyond that of > unique record "serial number". If one or more values are skipped due to > inputting weirdness, a record serial number gap is the benign, and only > result. R:azzak's gift code then always produces the desired value for a > given table. > > For your situation, in which AUTONUM'd key values are deleted along with > their records, I have no ready solution. I'd be interested to hear if the > SYS_NEXT approach works. > > Merry Christmas/Happy Holidays/Warmest Regards, > > Bruce Chitiea > SafeSectors, Inc. > 909.238.9012 <(909)%20238-9012> m > > ------ Original Message ------ > From: "classicmicro ." <classicmicrosyst...@gmail.com> > To: rbase-l@googlegroups.com > Sent: 12/6/2017 12:38:06 PM > Subject: Re: [RBASE-L] - Determining next autonum value > > Thanks -- Yes that would work if the table values could not be deleted -- > but if the last row were to be deleted it would no longer return the > correct value so it is not 100% bulletproof. For example, autonum > generates a value of 100 on a row insert, then row numbered 100 is deleted, > the calculation would come up with 99+1 (100) but the next autonum row > would actually be 101. > > If there is no other way, I've found that the next autonum value will be > stored in the SYS_DEFAULTS table in the SYS_NEXT column so it would be a > matter of identifying appropriate record based on SYS_COLUMN_ID that > relates to the table/column in question. I'm only concerned that the > SYS_COLUMN_ID if it were to change in the future through DB changes would > affect the code to lookup the next value. That's why I was looking for > some type of built-in function to return said value. > > > > > > > On Tue, Dec 5, 2017 at 5:49 PM, Bruce Chitiea <rby...@safesectors.com> > wrote: > >> SET VAR vNextAutonumID INTEGER = NULL >> >> SELECT ((MAX(AutonumID)) + 1) + >> INTO vNextAutonumID INDICATOR vInd + >> FROM Table >> >> Bruce Chitiea >> SafeSectors, Inc. >> 909.238.9012 <(909)%20238-9012> m >> >> ------ Original Message ------ >> From: "classicmicro ." <classicmicrosyst...@gmail.com> >> To: rbase-l@googlegroups.com >> Sent: 12/5/2017 5:45:05 PM >> Subject: [RBASE-L] - Determining next autonum value >> >> Using the statement SET VAR vnext = (NEXT(table,id)) I can determine the >> next autonum id number but it also increments the value. >> >> Is there a way to just determine prior to an INSERT or APPEND what that >> value will be without incrementing it? >> >> Thanks >> >> -- >> For group guidelines, visit http://www.rbase.com/support/u >> sersgroup_guidelines.php >> --- >> You received this message because you are subscribed to the Google Groups >> "RBASE-L" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to rbase-l+unsubscr...@googlegroups.com. >> For more options, visit https://groups.google.com/d/optout. >> >> -- >> For group guidelines, visit http://www.rbase.com/support/u >> sersgroup_guidelines.php >> --- >> You received this message because you are subscribed to the Google Groups >> "RBASE-L" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to rbase-l+unsubscr...@googlegroups.com. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > For group guidelines, visit http://www.rbase.com/support/ > usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to rbase-l+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > > -- > For group guidelines, visit http://www.rbase.com/support/ > usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to rbase-l+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
