Subject Re: [firebird-support] Re: Complex generators?
Author Jonathan Neve
ivoras wrote:

>--- In, "Florian Hector"
>>Create Trigger ADD_ID for MyTable
>>active before insert position 0
>>Declare Variable ID Integer;
>> Select Max(id) from MyTable WHERE TYPE=NEW.TYPE into :ID;
>> NEW.ID = :ID + 1;
>>I take it that you are aware of the possible problems that can
>bring. But if you create a unique
>>index on Type+ID, the server doesn't let you insert rows with the
>same combination of Type and ID.
>Yes, that was my first solution, and it fails when there are
>concurrent users. TYPE+ID is the primary key.
I have a similar situation. Here's how I get out of it:

I select max(field) where field like :condition, so as to find what
should be the next value. Then (in my application code), I do :

bool Error = true;
int nCode = MAX_VALUE;

while (Error) {
try {
DataSetFIELD->Value = nCode;
Error = false;
catch(...) {
Error = true;

This is of course pseudo-code. Using this method, you can be sure that
the values will be sequential, and you will also be sure that there can
not be any problem in a multi-user environnement, because if I can't
post, that means that there is another user in a different transaction
doing the same thing as me at the same time, and that user has already
set his PK. Therefore I then increment the value until I can post

This method relies on the fact that PK validation is transaction
independant, just like generators. So in a way, it amounts to the same
thing as the dynamic generator that you talked about.

Hope this helps!

Jonathan Neve.

[Non-text portions of this message have been removed]