ivoras wrote:

>--- In firebird-support@yahoogroups.com, "Florian Hector"
>
>
>>Create Trigger ADD_ID for MyTable
>>active before insert position 0
>>as
>>Declare Variable ID Integer;
>>begin
>> Select Max(id) from MyTable WHERE TYPE=NEW.TYPE into :ID;
>> NEW.ID = :ID + 1;
>>end
>>
>>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 {
DataSet->Edit();
DataSetFIELD->Value = nCode;
DataSet->Post();
Error = false;
}
catch(...) {
Error = true;
nCode++;
}
}

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
successfully.

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]