为什么相同的字段要SET两次呢?语法也应该是:UPDATE .... SET FIELD1=(SELECT...),FIELD2=(SELECT...)
或者UPDATE .... SET (FIELD1,FIELD2)=(SELECT FIELDVALUE1,FIELDVALUE2 FROM ...)
你用;连接肯定也是有问题啦.
再说你是对这个表的INSERT做触发,还对它进行查询,也会报错.
或者UPDATE .... SET (FIELD1,FIELD2)=(SELECT FIELDVALUE1,FIELDVALUE2 FROM ...)
你用;连接肯定也是有问题啦.
再说你是对这个表的INSERT做触发,还对它进行查询,也会报错.
后一个是stocknum而不是realnum
CREATE OR REPLACE TRIGGER OA_STOCK_QUANTITY_TR AFTER INSERT ON OA_ORIGINAL_STORE FOR EACH ROW
BEGIN
IF inserting THEN
IF :NEW.MATERIAL_ID IS NOT NULL THEN
UPDATE oa_goodsinfo
SET realnum = :NEW.ORIGINAL_QUANTITY,
stocknum= :NEW.ORIGINAL_QUANTITY
WHERE goodsid = :NEW.MATERIAL_ID);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END IF;
END IF;
END OA_STOCK_QUANTITY_TR;
有两个表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;
SET COSTPRICE =MONEY + (
SELECT SUM(ORIGINPRICE DISC * QUANTITY))) FROM OA_PURCHASEBILLDETAIL WHERE GOODSID = :NEW.GOODSID
)/STOCKNUM
WHERE GOODSID=:NEW.GOODSID;
这个公式:(Money + (ORIGINPRICE DISC * QUANTITY)) / (oa_goodsinfo.stocknum)有问题吗?注意在写触发器时,表的关联尽量少一点,如果确实需要,可以用临时变变量呀。