估不到oracle与ms sql触发器有如此区别:CREATE TRIGGER ti_input_base
before insert ON bj_input
for each row
BEGIN
--如果库存表中存在,更改库存
update bj_base
set amount_kc=amount_kc + :new.lm_sl,jinge=jinge+:new.lm_jinge,kl_date=sysdate
where code = :new.ls_code and ck_no=:new.ls_ckno;
--如果库存表中不存在,插入新库存
if sql%notfound then
insert into bj_base(code,amount_kc,sl,jinge,ck_no,tempfield,kl_date,id)
values(:new.ls_code,:new.lm_sl,:new.lm_sl,:new.lm_jinge,:new.ls_ckno,null,sysdate,:new.li_id);
--写进出总帐
insert into total_table (bj_id,code, op_date, note,
num, now_kc, kc_jinge,
in_out, fs_date, ck_no ,price )
select :new..id, :new.code, sysdate, '[入库]',
:new..sl, b.amount_kc, b.jinge,
1, sysdate, :new.ck_no,new.price
from bj_base b
where :new.id = b.id and :new.ck_no=b.ck_no;
END;
before insert ON bj_input
for each row
BEGIN
--如果库存表中存在,更改库存
update bj_base
set amount_kc=amount_kc + :new.lm_sl,jinge=jinge+:new.lm_jinge,kl_date=sysdate
where code = :new.ls_code and ck_no=:new.ls_ckno;
--如果库存表中不存在,插入新库存
if sql%notfound then
insert into bj_base(code,amount_kc,sl,jinge,ck_no,tempfield,kl_date,id)
values(:new.ls_code,:new.lm_sl,:new.lm_sl,:new.lm_jinge,:new.ls_ckno,null,sysdate,:new.li_id);
--写进出总帐
insert into total_table (bj_id,code, op_date, note,
num, now_kc, kc_jinge,
in_out, fs_date, ck_no ,price )
select :new..id, :new.code, sysdate, '[入库]',
:new..sl, b.amount_kc, b.jinge,
1, sysdate, :new.ck_no,new.price
from bj_base b
where :new.id = b.id and :new.ck_no=b.ck_no;
END;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货