> > What we did, is to have an internal PK value (by a generator),
> > insert the new records and once everything was inserted (saved)
> > to the database and the transaction was committed (as in: no
> > faults), we then did:
> >
> > UPDATE <table>
> > SET UserVisibleColumn = GEN_ID(mygenerator, 1);
> > (actually, the Oracle equivalent, but you get the idea).
> > I don't think we ever missed a number.
>
> Can you share some more information on this approach, please?

There isn't anything more to share - this is it.

>As in how
> do we actually implement something like this with
> Firebird/Delphi/IBObjects, for example? The approach sounds safe, and
> it's similar to what i've been thinking about, but i thought it would
> need a server-side ON_AFTER_COMMIT trigger ...:)

Simply like this:

- start transaction
- insert new record
- insert new record
- insert new record
- insert new record
- insert new record
< lots of records to insert, complex object structure, jadajadajada >
- commit transaction
(meaning, that the server side triggers etc all executed succesfully,
reserving materials etc etc )
- get next value from a generator
- start transaction
- update single record with that particular value
- commit transaction
- show the number to the user


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com