CREATE TRIGGER CNSB.YDDJ_T AFTER INSERT OR UPDATE OR DELETE
ON CNSB.YDDJ
FOR EACH ROW
declare v1 int; v2 int; num1 int; num2 int; cha1 int;cha2 int;
BEGIN
if inserting then
select count(*) into v1 from cnsb.ybgrljyzh1 where sbh=:new.sbh;
if v1>0 then
select ljzh into num1 from cnsb.ybgrljyzh1 where sbh=:new.sbh;
update cnsb.ybgrljyzh1 set ljzh=(:new.cszh+num1) where sbh=:new.sbh;elsif v1<=0 then
insert into cnsb.ybgrljyzh1 values(:new.sbh,:new.cszh);
select count(*) into v2 from cnsb.yb_jfnxb where sbh=:new.sbh;
if v2>0 then
select jfnx into num2 from cnsb.yb_jfnxb where sbh=:new.sbh;
update cnsb.yb_jfnxb set jfnx=(:new.cszh+num2) where sbh=:new.sbh;elsif v2<=0 then
insert into cnsb.jfnxb values(:new.sbh,:new.dwdm,:new.xm,0,:new.jfnx,0,0);
elsif updating then
cha1 := (:old.cszh - :new.cszh);
cha2 := (:old.jfnx - :new.jfnx);
select ljzh into num1 from cnsb.ybgrljyzh1 where sbh=:new.sbh;
update cnsb.ybgrljyzh1 set ljzh=(num1-cha1) where sbh=:new.sbh;
select jfnx into num2 from cnsb.yb_jfnxb where sbh=:new.sbh;
update cnsb.yb_jfnxb set jfnx=(num2-cha2) where sbh=:new.sbh;elsif deleting then
select ljzh into num1 from cnsb.ybgrljyzh1 where sbh=:old.sbh;
update cnsb.ybgrljyzh1 set ljzh=(num1-:old.cszh) where sbh=:old.sbh;
select jfnx into num2 from cnsb.yb_jfnxb where sbh=:old.sbh;
update cnsb.yb_jfnxb set jfnx=(num2-:old.jfnx) where sbh=:old.sbh;end if;
end;
ON CNSB.YDDJ
FOR EACH ROW
declare v1 int; v2 int; num1 int; num2 int; cha1 int;cha2 int;
BEGIN
if inserting then
select count(*) into v1 from cnsb.ybgrljyzh1 where sbh=:new.sbh;
if v1>0 then
select ljzh into num1 from cnsb.ybgrljyzh1 where sbh=:new.sbh;
update cnsb.ybgrljyzh1 set ljzh=(:new.cszh+num1) where sbh=:new.sbh;elsif v1<=0 then
insert into cnsb.ybgrljyzh1 values(:new.sbh,:new.cszh);
select count(*) into v2 from cnsb.yb_jfnxb where sbh=:new.sbh;
if v2>0 then
select jfnx into num2 from cnsb.yb_jfnxb where sbh=:new.sbh;
update cnsb.yb_jfnxb set jfnx=(:new.cszh+num2) where sbh=:new.sbh;elsif v2<=0 then
insert into cnsb.jfnxb values(:new.sbh,:new.dwdm,:new.xm,0,:new.jfnx,0,0);
elsif updating then
cha1 := (:old.cszh - :new.cszh);
cha2 := (:old.jfnx - :new.jfnx);
select ljzh into num1 from cnsb.ybgrljyzh1 where sbh=:new.sbh;
update cnsb.ybgrljyzh1 set ljzh=(num1-cha1) where sbh=:new.sbh;
select jfnx into num2 from cnsb.yb_jfnxb where sbh=:new.sbh;
update cnsb.yb_jfnxb set jfnx=(num2-cha2) where sbh=:new.sbh;elsif deleting then
select ljzh into num1 from cnsb.ybgrljyzh1 where sbh=:old.sbh;
update cnsb.ybgrljyzh1 set ljzh=(num1-:old.cszh) where sbh=:old.sbh;
select jfnx into num2 from cnsb.yb_jfnxb where sbh=:old.sbh;
update cnsb.yb_jfnxb set jfnx=(num2-:old.jfnx) where sbh=:old.sbh;end if;
end;
解决方案 »
- ORA-29532 ora_06512 请各位帮忙解决一下,谢谢~ 附图
- over函数
- 急:竖表如何实现成横表?
- ●如何监控SQL进程?●
- oracle导入数据库出错?
- 求教一个SQL语句,急???
- 数据量才500,600字就插不进数据库了(在线等!)
- 求救,在win2Kpro上安装完Oracle9i,点击Enterprise manage console后一闪而过???
- 有没有低级一点的入门书或者是电子书下载啊?
- 求助!alter system set statistics_level=basic时告诉我数据库不可用,怎么解决?
- 急!!!!!!!!!!!!oracle存储过程
- 求购中小型生产企业ERP软件
还好我自己找出来勒!嘿嘿... ^ _ ^