这个T简单 Trigger 里面出现两次 if sql%RowCount = 0 then 的判断是否执行插入新数据。
在recd表中插入新数据触发器没有问题, 但是只要进行更新或删除操作时, 第二个判断一直为True (第一个为False),于是
我换成 if sql%NotFound then 也依然为True, 导致更新和操作失败。无奈我将第二个判断换成 if Inserting then 才解决问题。
麻烦大牛们给小弟解释下为什么出现这样?
create or replace
Trigger TRI_recd
after update or insert or delete of rec_qty ON recd
for each row
Declare
v_rec_date recm.rec_date%type;
v_stock_id recm.stock_id%type;
v_pur_price purdd.pur_price%type;
v_odr_no purplanm.odr_no%type;
v_req_seq purplanm.req_seq%type;
Begin
select rec_date, stock_id into v_rec_date, v_stock_id
from recm
where rec_no = nvl(:new.rec_no, :old.rec_no);
select pur_price, odr_no, req_seq into v_pur_price, v_odr_no, v_req_seq
from purdd
where purd_no = nvl(:new.purd_no, :old.purd_no)
and purd_seq = nvl(:new.purd_seq, :old.purd_seq);
update purplanm
set rec_qty = nvl(rec_qty, 0) + nvl(:new.rec_qty, 0) - nvl(:old.rec_qty, 0)
where odr_no = v_odr_no
and req_seq = v_req_seq;
update purdd
set rec_qty = nvl(rec_qty, 0) + nvl(:new.rec_qty, 0) - nvl(:old.rec_qty, 0)
where purd_no = nvl(:new.purd_no, :old.purd_no)
and purd_seq = nvl(:new.purd_seq, :old.purd_seq);
update STOCKM
set on_way_qty = nvl(on_way_qty, 0) + nvl(:new.rec_qty, 0) - nvl(:old.rec_qty, 0),
stock_qty = nvl(stock_qty, 0) + nvl(:new.rec_qty, 0) - nvl(:old.rec_qty, 0),
stock_amt = (nvl(stock_qty, 0) + nvl(:new.rec_qty, 0) - nvl(:old.rec_qty, 0)) * stock_price
where mat_no = nvl(:new.mat_no, :old.mat_no);
update stockd
set stock_qty = nvl(stock_qty, 0) + nvl(:new.rec_qty, 0) - nvl(:old.rec_qty, 0),
stock_amt = (nvl(stock_qty, 0) + nvl(:new.rec_qty, 0) - nvl(:old.rec_qty, 0)) * stock_price
where mat_no = nvl(:new.mat_no, :old.mat_no)
and stock_id = v_stock_id;
if sql%ROWCOUNT = 0 then --第一个判断
insert into stockd values(v_stock_id, :new.mat_no, :new.rec_qty, v_pur_price, :new.rec_qty * v_pur_price);
end if;
update stockdd
set stock_qty = nvl(stock_qty, 0) + nvl(:new.rec_qty, 0) - nvl(:old.rec_qty, 0),
stock_amt = (nvl(stock_qty, 0) + nvl(:new.rec_qty, 0) - (nvl(:old.rec_qty, 0)) * stock_price)
where stock_id = v_stock_id
and stock_yymm = to_char(v_rec_date, 'YYYYMMDD')
and mat_no = nvl(:new.mat_no, :old.mat_no);
if INSERTING then -- 第二个判断
insert into stockdd values(substr(to_char(v_rec_date, 'YYYYMMDD'), 1, 6), v_stock_id, :new.mat_no, :new.rec_qty, v_pur_price, :new.rec_qty * v_pur_price);
end if;
end;
------解决思路----------------------
影响的sql操作都有影响数据么 是不是删除或更新了不存在的数据
------解决思路----------------------
根绝if sql%NotFound then 也依然为True这个现象看应该是第二个判断之前的update没更新到数据