create or replace trigger OP.update_lis_statue
after insert on op.op_charge
for each row
begin
if :new.sum_total < 0 then
update ht.yj_tbs_jycomapplym t set t.fcancelflag = '1'
where t.fpatno in(select a.visit_no from op.op_visit a,op.op_charge b
where a.visit_id = b.visit_id and b.charge_id = :new.charge_id)
and t.fitemno in(select c.item_id from op.op_charge c
where c.charge_id = :new.charge_id);
end if;
end update_lis_statue;
after
for
begin
end
以上触发器是实现退费后更新某表的标志功能,虽然在pl/sql编译通过,但使用中报错如下:
ORA-04091: table OP.OP_CHARGE is mutating, trigger/function may not see it
ORA-06512: at "OP.UPDATE_LIS_STATUE", line 3
ORA-04088: error during execution of trigger 'OP.UPDATE_LIS_STATUE'
ORA-06512: at "OP.UPDATE_LIS_STATUE", line 3
ORA-04088: error during execution of trigger 'OP.UPDATE_LIS_STATUE'
分析如下:
改代码,解决如下:
create or replace trigger OP.update_lis_statue
after insert on op.op_charge
for each row
begin
if :new.sum_total < 0 then
update ht.yj_tbs_jycomapplym t set t.fcancelflag = '1'
where t.fpatno in(select a.visit_no from op.op_visit a
where a.visit_id = :new.visit_id)
and t.fitemno = :new.item_id;
end if;
end update_lis_statue;
after
for
begin
end
行级操作,其实只要取当前插入行的数据作为约束条件就行了,当初是想太多了