| Subject | Re: [IBO] Generators as rows IDs |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-03-30T23:33:04Z |
At 07:31 AM 31/03/2008, you wrote:
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.
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
Helen
> I use IBO and Firebird in a simple Delphi book catalog program IYou 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.
>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?
>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.
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.
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
>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