Clay Shannon wrote:

>We want sequential "admission numbers" in our app to be auto-generated for
>the user (they need to see them, but getting the next sequential value
>automatically rather than relying on the data entry people to always
>accurately know what the next one should be).
>
>
>
>A generator won't work, I don't think, because we don't want any missing
>values. IOW, if the user starts a record, and gets number 123445 and then
>for some reason doesn't complete the record, that number will be unused and
>there will be a hole in the sequencing.
>
>
>
>If I could wait until just before the post to grab the number, that would
>probably solve it, but again, the user wants to see this number all through
>the data-entry process.
>
>
>
>Any ideas on the best way to implement something like this? The best thing
>I've been able to think of so far is to select max(admission_number) from
>the table and use the next number through the data-entry process, but query
>for that value again just before posting and, if it has changed, increment
>the number again and inform the user of the numbering change. There must be
>a better way, though.
>
>
>
>Clay Shannon,
>
>Dimension 4 Software
>
>
>
>

Clay,

Even if you get the generator value in the "post" moment, or let a
before insert trigger populate it for you, this will not ensure that no
values will be skiped, a lot of factors will make a generator value be
skiped (any problem on the insert phase like FK violation, unique
constraints violation, check constraints violation, a simple rollback, etc.)

There is an article that is the main reference for this topic, and was
suggested here before every time someone comes with the same needs, take
a look on this:

http://www.ibobjects.com/docs/ti_AuditableSeries.ZIP

and search the archives to see what the gurus says about it.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005