Subject | should record locking required in this scenario? |
---|---|
Author | Michael Fung |
Post date | 2004-08-31T03:48:35Z |
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
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