有PS_MATERIAL (商品表) 字段 price(价格) material_no(商品编号)
PS_MATERIAL_price(商品价格表) price(价格) material_no(商品编号) 商品表外键当我页面修改商品价格表中的价格后 对应的商品表也修改价格
下面是我写的触发器 不知道为什么 我修改价格后 触发器 没有修改对应的商品信息
CREATE OR REPLACE TRIGGER PS_MATERIAL_PRICE_UPDATE
AFTER INSERT OR UPDATE OR DELETE
ON SPPSAPPS.PS_MATERIAL_PRICE REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
var_mat PS_MATERIAL%rowtype;
BEGIN IF INSERTING or UPDATING THEN
SELECT * into var_mat FROM PS_MATERIAL WHERE MATERIAL_NO = :New.MATERIAL_NO; IF (:New.price <> var_mat.price) or var_mat.price is null THEN
update ps_material mat set mat.price = :New.price where mat.material_no = :New.material_no;
END IF;
IF (:New.inventory <> var_mat.quantity) or var_mat.quantity is null THEN
update ps_material mat set mat.quantity = :New.inventory where mat.material_no = :New.material_no;
END IF;
END IF; IF DELETING THEN
update ps_material mat set mat.price = null,mat.quantity=null where mat.material_no = :New.material_no;
END IF;END PS_MATERIAL_PRICE_UPDATE;
PS_MATERIAL_price(商品价格表) price(价格) material_no(商品编号) 商品表外键当我页面修改商品价格表中的价格后 对应的商品表也修改价格
下面是我写的触发器 不知道为什么 我修改价格后 触发器 没有修改对应的商品信息
CREATE OR REPLACE TRIGGER PS_MATERIAL_PRICE_UPDATE
AFTER INSERT OR UPDATE OR DELETE
ON SPPSAPPS.PS_MATERIAL_PRICE REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
var_mat PS_MATERIAL%rowtype;
BEGIN IF INSERTING or UPDATING THEN
SELECT * into var_mat FROM PS_MATERIAL WHERE MATERIAL_NO = :New.MATERIAL_NO; IF (:New.price <> var_mat.price) or var_mat.price is null THEN
update ps_material mat set mat.price = :New.price where mat.material_no = :New.material_no;
END IF;
IF (:New.inventory <> var_mat.quantity) or var_mat.quantity is null THEN
update ps_material mat set mat.quantity = :New.inventory where mat.material_no = :New.material_no;
END IF;
END IF; IF DELETING THEN
update ps_material mat set mat.price = null,mat.quantity=null where mat.material_no = :New.material_no;
END IF;END PS_MATERIAL_PRICE_UPDATE;
select t.trigger_name,t.status from user_triggers t;
确认你的触发器执行时有没有产生异常,
如你的商品一价格表MATERIAL_NO是1对1的关系吗?
否则,你的select into隐式游标会产生异常。