Hum, so the main problem would be inserting the same value in the same table
at same time?

Thinking about it, i think i can insert the new value in the table if it
doesnt exist and then always update it in the trigger event, so i can
always, insert the new value, commit imediatelly and after that start my
transaction.

Would it be better?

The method about using the table would work too, off course i have a table
with all the individual item values, but i like to keep that just for
reports and in case of something goes wrong and i have to recalc the total
stock of an item... i think it would be better to avoid that, i m afraid
this can slow down everything after i have too many rows there.

Anyway, i m newbie so am open to any suggestions.

Thanx for your help,

-Fábio

On 7/28/06, Adam <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com<firebird-support%40yahoogroups.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]
> >
>
>
>


[Non-text portions of this message have been removed]