请教一个关于触发器问题
有两个表oa_goodsinfo,oa_purchasebilldetail.
我想计算oa_goodsinfo表中的costprice的值.它是通过这两个表计算得来的.
是由Money字段的值和表oa_purchasebilldetail表里面的ORIGINPRICE DISC * QUANTITY
计算公式为:(Money + (ORIGINPRICE DISC * QUANTITY)) / (oa_goodsinfo.stocknum)
请高人们在帮帮忙.
CREATE OR REPLACE TRIGGER OA_CALC_CostPrice_TR
AFTER INSERT OR UPDATE ON OA_PURCHASEBILLDETAIL
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE OA_GOODSINFO
SET COSTPRICE = ((SELECT MONEY FROM OA_GOODSINFO)
+ (SELECT sum(ORIGINPRICE DISC * QUANTITY)))/ (select stocknum from oa_goodsinfo)
FROM OA_PURCHASEBILLDETAIL
WHERE OA_GOODSINFO.GOODSID = OA_PURCHASEBILLDETAIL.GOODSID;
END IF;
EXCEPTION WHEN OTHERS THEN
RAISE;
END OA_CALC_CostPrice_TR;