Helen Borrie wrote:

>
> At 07:31 AM 31/03/2008, you wrote:
> > I use IBO and Firebird in a simple Delphi book catalog program I
> > developed. The PKs consist in firebird generators, triggered to
> > increment before each insert, as usual.The problem with this method
> > (not for me, of course, but for a client of mine) is that when we
> > insert and do not commit, the ID (the table PK) is lost, and
> > therefore not used in the next insert. This really annoys my
> > client, because I use the ID in grids, and as search parameters.
> > So, what do you suggest me?
>
> You have made a design error. You should never use a field that has
> significance as data for your primary key. Make a separate, unique
> field as a "serial number". Do not show the PK to the user at all.

I can see it clearly now, it's a bad idea to show the user the PK. I
agree with the avoidance of using of a field with a significance as
data, although it's common to use Social Security number for PK, for
example. Would that be wrong also?

>
> > Is it possible for IBO to increment the value only after commit?
>
> Of course not. Nothing can commit an insert if the primary key is
> missing.

Yes, I know, I thought maybe using a temporary value, but I can see it's
a bad ideia.

> There are "tricks" that you can do to avoid generating and fetching
> the key value, such as using a dummy value that will be replaced by a
> trigger, or using custom InsertSQL that excludes the PK from the
> insert statement list. The disadvantages are greater than the
> advantages, although in Fb 2 and above you can retrieve the value of
> the key after the commit using a RETURNING clause. But using the PK
> as meaningful data is still a design error and it will bite you in
> other ways, particularly if your book table has dependencies.

That's why I want to correct it ASAP.

> I recommend that you review your design and consider adding this
> serial number field and adding another generator for exclusive use
> for the serial number. You can rewrite the flow of your insert
> procedure with care, so that you fetch the serial number from a
> (different) generator in the AfterCommit event of the transaction and
> update the serial number field in a new transaction. This may be
> sufficient for your needs. If you need tighter control of the serial
> numbers then you can find some more complex approaches in the
> TechInfo sheet An Auditable Series of Numbers, at
> www.ibobjects.com/TechInfo.html

I think what you described will do the work fine, but I will read the
article carefully. Regarding the correctness of the serial number when
deleting records, any suggestions?

> > How will that affect my program?
>
> What you asked about can not work. Nothing, not even IBO, is allowed
> to break the integrity rules of the database.
>
> Helen
>

Thank you very much!

>