--- In firebird-support@yahoogroups.com, "Fabio Gomes" <fabioxgn@...>
wrote:

>
> So what do you advice me to do?
>
> I already have another software( in php) that use this method, and i

never

> had problems, i simply wrote a function if the current quantity isnt

correct

> that function will sum the history and correct things up, thats why i m
> thinking about using the same method in this too.

The problem is that the method implies updates to the quantity are
serialised. This will present itself in the form of lock conflicts if
two concurrent transactions attempt to change the same stock. This may
or may not be a problem depending on business process.

As far as your existence check works, it is going to be difficult
because your transaction is unable to see uncommitted inserts in your
'other' table.

If you have a unique constraint of primary key based upon this stock
number, then you will get a violation if another uncommitted
transaction is already in the process of inserting that value, but you
will not be able to update it.

Dimitry's suggestion is quite sound.

Another solution is to use a table that can hold multiple records for
a single stock item, and have your triggers add a +1 after insert or a
add a -1 after delete. On a cycle, run a procedure that summarises
this table, and calculating the stock count will be reasonably cheap.
The benefit of this sort of approach is that you do not have a problem
if two people want to update the same stock at the same time.

Adam

>
> On 7/28/06, Dimitry Sibiryakov <SD@...> wrote:
> >
> > On 27 Jul 2006 at 17:22, Fabio Gomes wrote:
> >
> > >I ll try to explain what i need.. i have 2 tables, one that have the
> > >general stock history, and other with the current stock of each item
> > >(i used the serial number as primary key).
> >
> > What you are talking about is usually called "stored aggregates"
> > and is not recommended to be used without absolute necessity.
> > Keeping current quantity by triggers may be troublesome in
> > multiuser environment. More often the quantity is kept for some date
> > in the past and current quantity is computed as that quantity + sum
> > of operations since then.
> >
> > --
> > SY, Dimitry Sibiryakov.
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>