This design needs to be normalised, or you will get real problems with
performance

Something like this:

StockControl
(id, pro_id, stock_added)

Stock
(pro_id, actual_stock);

*** Stock_added is negative when you remove and positive otherwise

Now create some triggers on StockControl to manage stock

CREATE TRIGGER SCI ACTIVE AFTER INSERT AS
BEGIN
UPDATE STOCK
SET ACTUAL_STOCK = ACTUAL_STOCK + NEW.STOCK_ADDED
WHERE PRO_ID = NEW.PRO_ID;
END
^

Ok, now every time you add something to stock control, the stock count
is updated in the stock table.

If you want that information, a simple join will do it.

SELECT SC.id, SC.pro_id, SC.stock_added, S.ACTUALSTOCK
FROM STOCKCOUNT SC
JOIN STOCK S ON (SC.PRO_ID = S.PRO_ID)

Judging by the design, you would do well to spend a few hours reading
about normalisation.

Hope that helps.
Adam

--- In firebird-support@yahoogroups.com, "fabioxgn" <fabioxgn@g...> wrote:
>
> Hi all, i m making a little website for a store using firebird 1.5 and
> php, and it have a stock control table.. so i m using a table with the
> following fields:
>
> id, product_id, action(add or remove), quantity and actual_stock
>
> its kinda simple, my table data is like this:
>
> id, pro_id, action, quantity, actual_stock
> 1, 01, A, 10, 0
> 2, 01, R, 1, 9
> 3, 01, R, 1, 8
> 4, 01, R, 1, 7
> 5, 01, R, 2, 5
> 6, 01, A, 5, 10
>
> but my problem is... i have to somethimes remove a row, and i have to
> rewrite all the atual_stock of the product.. like, if i remove the
> second entry.. i have to do it:
>
> id, pro_id, action, quantity, actual_stock
> 1, 01, A, 10, 0
> 2, 01, R, 1, 9<-Remove
> 3, 01, R, 1, 9<-Update
> 4, 01, R, 1, 8<-Update
> 5, 01, R, 2, 6<-Update
> 6, 01, A, 5, 11<-Update
>
> So it have to update the rows after the one that was removed.. but i
> dont have idea how to do it... my table should look like this after
> the previuos delete:
>
>
> id, pro_id, action, quantity, actual_stock
> 1, 01, A, 10, 0
> 3, 01, R, 1, 8
> 4, 01, R, 1, 7
> 5, 01, R, 2, 5
> 6, 01, A, 5, 10
>
> btw. i cant figure out how to do it with firebird, i tried to think in
> a solution but there are too many stuff to update.. as i m noob in
> databasesi dont know how
>
> Please, any help and ideas are welcome, hope you understand where i m
> trying to get at ^^
>
> Thanx in advance,
>
> F�bio.
>