Dear all,

The situation is:
I need to update the average cost of a product item whenever new
purchased item is entered to the database.
That is done by calling a procedure in the "after insert" trigger of
Purchased_Item table:
execute procedure set_product_avg_cost (new.product_id, new.price,
new.qty);

And the procedure defined as follows:

CREATE PROCEDURE SET_PRODUCT_AVG_COST (
PRODUCT_ID NUMERIC (18, 0),
PRICE NUMERIC (15, 6),
DELTA_QTY INTEGER)
AS

DECLARE VARIABLE OLD_QTY INTEGER;
DECLARE VARIABLE OLD_AVG_COST NUMERIC(15,6);
DECLARE VARIABLE OLD_TTL_VALUE NUMERIC(15,6);
DECLARE VARIABLE NEW_AVG_COST NUMERIC(15,6);

BEGIN

/* step 1. lock that product */
UPDATE PRODUCT SET ID=ID WHERE ID = :PRODUCT_ID;

/* step 2. get old qty and old ttl value */
SELECT PHY_QTY, AVG_COST FROM PRODUCT WHERE ID=:PRODUCT_ID
INTO :OLD_QTY, :OLD_AVG_COST;

IF (OLD_AVG_COST IS NULL) THEN OLD_AVG_COST = 0;

OLD_TTL_VALUE = OLD_QTY * OLD_AVG_COST;

/* step 3. calc new avg cost */
NEW_AVG_COST = (OLD_TTL_VALUE + (DELTA_QTY * PRICE)) /
(OLD_QTY + DELTA_QTY);

/* step 4. /
UPDATE PRODUCT
SET AVG_COST = :NEW_AVG_COST
WHERE ID = :PRODUCT_ID;

END


I am using FB 1.5.1. Transaction set as WAIT READ COMMITTED
RECORD_VERSION

I just want to know if "step 1" is needed or not? Any better
alternatives?


TIA,
Michael