Milan,

I guess this is one way to do the autonumber thing. But in your solution you
keep an update transaction open the entire time of the action. I am no
expert but I guess an update or insert transaction should be as short as
possible.

Personally I use a generator an a SP to get my billing numbers. I made
business logic so that I will never loose a number (e.g. if a user cancels
an action, the record is stored for future use. It get's a special status
and a color in a grid). In this case it are contract numbers that need to be
auditable.

Also on these tables I always use a PK (generator + trigger) that has
nothing to do with the data in the record.

For Billing, I get the billing number at the moment the bill gets stored.
Since only at that moment the generator is called I will never loose a
number if the client cancels the action.

But as told in my other mail there is an excelent article on the site of
IBobjects on this subject. Also Helen has given some advice on this in the
past (just view old messages).

Benno

----- Original Message -----
From: "Milan Babuskov" <milanb@...>
To: <ib-support@yahoogroups.com>
Sent: Saturday, January 04, 2003 1:40 PM
Subject: Re: [ib-support] Number Skipping


> benno wrote:
> > Hi,
> >
> > This problem has nothing to do with IB or FB. You use an SP to update
the
> > number. Transactions can not "see" each others transactions until
commit, so
> > you'll be wrong in your number here (at least in a multiuser
environment).
> >
> > Use a generator to generate your billing numbers, they work outside
> > transaction scope and are garanteed unique.
>
> Generators are outside of transaction control, so if the transaction is
> rolled back, the numbers will still go up. You need some safer way to do
> this.
>
> I have table counter with rows:
> record_id
> table_name
>
> When I insert new row, I begin the transaction and do:
> select record_id
> from counter
> where table_name = 'table I'm updating'
> into :new_id_value;
>
> new_id_value = new_id_value + 1;
>
> update counter set record_id = :new_id_value;
>
> This locks the record in table counter for other users, and they cannot
> get a new number until transaction is commited or rolled back. If
> transaction is rolled back, the update statement's effect are gone, and
> the next user can get that unused number.
>
> This is just one of the ways to do it.
>
> If you want more help, please post more detailed explanation of your
> system (what exactly do does SP's do? how do you get the numbers? do you
> use generators? etc...)
>
> --
>
> Milan Babuskov
> http://fbexport.sourceforge.net
>
>
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>