At 08:50 PM 7/02/2006, you wrote:

>Don't take care, for my problem I had a solution valid
>
>the only one trouble, is to optimize the acces to found this "max" value
>
>I will try with both index/constraints

No. Your reasoning is wrong about the concurrency issue.

I suggest that, instead, you create a table of document numbers,
which are to be used ONLY for numbering documents, never as record
keys. Its format will be document number, plus a boolean "used",
which is nullable, plus any other needed fields, such as
AccountingPeriod. Load this up with as many document numbers as you
need for a cycle (as determined by your requirements).

At invoice creation time, pass a temporary value to the document id
that will be picked up by a Before Insert trigger. Use the trigger
to a) update the first record in the doc number table that has used
is null, setting used = 'T' and b) to read that doc number into the
invoice doc number.

Two transactions can't update the same doc number record, so, the
first to post the update will win. Your transaction should be No
Wait and your trigger must handle such conflict exceptions itself and
move on through the doc numbers until it gets one that is not in conflict.

This does cause a bottleneck if you have a lot of users creating
invoices at once, but it prevents the situation where you have
duplicate doc numbers being created in different transactions because
each transaction has a different view of the old Max().

./heLen