Reading the post by you and Zvjez I think I am getting both the ideas. I
like the Zvjez's idea of a separate table for counter (this an any other
serials I need, for eg. Invoice no.) but reading your posts it seems the
number even if updated might not able be to be seen in the current
transaction and might lead to duplications.

Plus I cannot have the number generated when the record for Batch is
started as I can only have it after I know the Item iID as the
data-entry progresses.

Any middle grounds? I already have codes in my application framework to
handle generators and not allowing to have missing numbers in case the
current records was cancelled. I can point the code to use this table
instead if it can be made as transparent as generators, like once
updated any further check in the next fraction of a second will provide
this new number.

Thanks Milan and Zvjez

Kind regards
Bhavbhuti

On 29/04/2010 03:50 pm, Milan Tomeš - Position wrote:
> Because of transaction control it cannot be succesfully done.
>
> Milan
>
> Dne 29.04.2010 11:30, zvjezdantomicevic napsal(a):
>
>> Sure.
>> But with some unique constraints and transaction control it might be done.
>> Also, generators can present a problem if numbers must be consequent.
>> Best of luck
>>
>> --- In firebird-support@yahoogroups.com
>> <mailto:firebird-support%40yahoogroups.com>, Milan Tomeš - Position
>> <tomes@...> wrote:
>>
>>> That's not a good solution because of possible duplicity. Generators
>>>
>> are
>>
>>> not under the transaction control, but triggers (in fact data modified
>>> by triggers) are.
>>>
>>> Milan
>>>
>>> Dne 29.04.2010 10:37, zvjezdantomicevic napsal(a):
>>>
>>>> Just from top of my head:
>>>> How about creating table
>>>> ITEM_ID int
>>>> YEAR int
>>>> COUNTER int
>>>> and use triggers.
>>>> If record for that year does not exist,insert it with counter 0,
>>>> and if it does, increase counter by 1 using trigger.
>>>>
>>>> You have if exists, insert or update statements, and before
>>>> insert/update triggers at your disposal to do this.
>>>>
>>>> --- In firebird-support@yahoogroups.com
>>>>
>> <mailto:firebird-support%40yahoogroups.com>
>>
>>>> <mailto:firebird-support%40yahoogroups.com>, "venussoftop"
>>>> <venussoftop@> wrote:
>>>>
>>>>> It think it is necessary, from my POV, please correct me if I am
>>>>>
>>>> thinking wrong. The numbers are for batch no. of pharmacy products
>>>>
>> and
>>
>>>> numbering is restarted every financial year and because they are used
>>>> on consumer readable labels I need them as integers. The generated
>>>> numbers look like this finally ITM001-505-09/10 where ITM001 is the
>>>> product code, 505 the generator number and 09/10 the financial year.
>>>>
>>>>> Thanks and regards
>>>>> Bhavbhuti
>>>>>
>>>>> --- In firebird-support@yahoogroups.com
>>>>>
>> <mailto:firebird-support%40yahoogroups.com>
>>
>>>> <mailto:firebird-support%40yahoogroups.com>, "zvjezdantomicevic"
>>>> <zvjezdantomicevic@> wrote:
>>>>
>>>>>> Well, this usage of generators is a bit uncommon, and may not
>>>>>>
>> be a
>>
>>>> sound design.
>>>>
>>>>>> Philippe suggested that you use globaly unique identifiers (GUID)
>>>>>>
>>>> instead of integers.
>>>>
>>>>>> They sound like good solution in this case, unless it is
>>>>>>
>>>> absolutely essential to use integers and restart them every year.
>>>>
>>>>>> --- In firebird-support@yahoogroups.com
>>>>>>
>> <mailto:firebird-support%40yahoogroups.com>
>>
>>>> <mailto:firebird-support%40yahoogroups.com>, Bhavbhuti Nathwani
>>>> <venussoftop@> wrote:
>>>>
>>>>>>> Thanks Philippe, but I am sorry I did not understand what you
>>>>>>>
>>>> mean to say I
>>>>
>>>>>>> do.
>>>>>>>
>>>>>>> Kind regards
>>>>>>> Bhavbhuti
>>>>>>>
>>>>>>>
>>>> _,___
>>>>
>>>
>>> [Non-text portions of this message have been removed]
>>>
>>>
>>
>>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>