With heavy concurrent use, updating the quantity field directly is a bad
idea.

Rather, add another table:

Create table ChangedStock(
ID INTEGER NOT NULL PRIMARY KEY,
PRODUCT_ID INTEGER NOT NULL,
QUANTITY_CHANGE INTEGER)

Then, insert into this table every time anything changes (leaving your
products table unchanged). If you add 2 items to product 15, then run

INSERT INTO ChangedStock(PRODUCT_ID, QUANTITY_CHANGDE) VALUES(15, 2)

and if you remove two items from your stock, then

INSERT INTO ChangedStock(PRODUCT_ID, QUANTITY_CHANGDE) VALUES(15, -2)

Then, at certain intervals (e.g. at night), run the following two
queries in the same transaction.

UPDATE PRODUCTS P
SET P.QUANTITY = P.QUANTITY +
(SELECT SUM(CS.QUANTITY_CHANGE)
FROM ChangedStock CS
WHERE CS.PRODUCT_ID = P.PRODUCT_ID);

DELETE FROM ChangedStock

To check that there still is items in stock, you have to check both the
products table and the ChangedStock table and there is still a
possibility that two concurrent transactions may sell more stock than
you actually have. If this is important to avoid, I suppose your program
could check, in a new transaction started after you committed your
update, that the stock is still positive or empty. If transactions
concurrently update the same item, then this risks that both are fails
this check, when one of them could have succeeded. I don't know of any
way short of serialising inserts (acceptable in many cases, but a very
bad idea in highly concurrent environments) that gets the correct stock
in every situation, but the way I described a few lines back, makes it
possible for you to choose whether you want to risk selling products you
don't have or tell the customer that you're sold out when in fact there
could be enough in stock for her.

HTH,
Set

Fabio Gomes wrote:

> Hi guys,
>
> I have another question :)
>
> The solution using SUM() and a table with quantitys is working fine
> now, but i have another doubt.
>
> I have my products table:
>
> product_id
> description
> quantity
>
> This quantity, is the sum of all the records of the product in the
> other table, wich would be the best way to keep it updated?
>
> Should i use a query with a join and SUM() each time i need to see the
> quantity?
>
> Or sould i create a view? or maybe a computed field (i dont have any
> idea how it works btw)? Or can i even use a trigger?
>
> Which would be the best way to do it?
>
>
> Thanx in advance,
>
> -F=E1bio.