create or replace trigger aaa before insert ON a FOR EACH ROW declare vcount INTEGER; BEGIN select count(*) into vcount from a; update b set id=(id*vcount+:new.id )/(vcount+1); END;
这个问题可以借助新建第三个表格来实现这个功能,在oracle中,不允许对触发触发器的表格进行操作,可以先创建一个表格和第一个表格完全相同(当然这要带来一定的浪费,特别是数据量很大时),然后通过创建表一和表三的关联,当对表一进行操作时,在触发触发器的同时,更新表三,这样触发器只要对表三进行统计即可得到同样的效果,对于数据量不是很大时,是可以的 其中有三个表(t_wlrycjl,t_wlrycjl_bak,t_jdl) create or replace trigger tri_wlrycjl after insert or delete or update on t_wlrycjl for each row declare i int; j int; begin if inserting then select count(wcjdj) into i from t_jark_wlrycjl_bak where wcjdj='0' and khyf=:new.khyf and jdmc=:new.jdmc; select count(wcjdj) into j from t_jark_wlrycjl_bak where khyf=:new.khyf and jdmc=:new.jdmc; insert into t_wlrycjl_bak values(:new.JDMC,:new.KHYF,:new.XLH,:new.WLRXM,:new.ZJHM,:new.KHRQ,:new.RZRQ,:new.ZZZDZGX,:new.ZZZDJD,:new.FWDJZZCJ,:new.JWH,:new.YLSJZZ,:new.WCJDJ,:new.XJZDZ ,:new.CJRJH,:new.CJSJ ); if :new.wcjdj='0' then update t_jdl set wlrycjl=floor((j-i)/(j+1)*100) where khyf=:new.khyf and jdmc=:new.jdmc; else update t_jdl set wlrycjl=floor((j-i+1)/(j+1)*100) where khyf=:new.khyf and jdmc=:new.jdmc; end if; else if deleting then select count(wcjdj) into i from t_wlrycjl_bak where wcjdj='0' and khyf=:old.khyf and jdmc=:old.jdmc; select count(wcjdj) into j from t_wlrycjl_bak where khyf=:old.khyf and jdmc=:old.jdmc; delete from t_wlrycjl_bak where wlrxm=:old.wlrxm and zjhm=:old.zjhm; if :old.wcjdj='0' then update t_jdl set wlrycjl=floor((j-i)/(j-1)*100) where khyf=:old.khyf and jdmc=:old.jdmc; else update t_jdl set wlrycjl=floor((j-i-1)/(j-1)*100) where khyf=:old.khyf and jdmc=:old.jdmc; end if; else if updating then select count(wcjdj) into i from t_wlrycjl_bak where wcjdj='0' and khyf=:new.khyf and jdmc=:new.jdmc; select count(wcjdj) into j from t_wlrycjl_bak where khyf=:new.khyf and jdmc=:new.jdmc; update t_wlrycjl_bak set JDMC=:new.JDMC where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set KHYF=:new.KHYF where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set XLH=:new.XLH where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set ZJHM=:new.ZJHM where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set KHRQ=:new.KHRQ where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set RZRQ =:new.RZRQ where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set ZZZDZGX=:new.ZZZDZGX where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set ZZZDJD=:new.ZZZDJD where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set FWDJZZCJ=:new.FWDJZZCJ where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set JWH =:new.JWH where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set YLSJZZ=:new.YLSJZZ where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set WCJDJ=:new.WCJDJ where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set XJZDZ=:new.XJZDZ where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set CJRJH=:new.CJRJH where wlrxm=:old.wlrxm and zjhm=:old.zjhm; update t_wlrycjl_bak set CJSJ=:new.CJSJ where wlrxm=:old.wlrxm and zjhm=:old.zjhm; if (:old.wcjdj=0 and :new.wcjdj=0) or (:old.wcjdj=1 and :new.wcjdj=1) then update t_jdl set wlrycjl= floor((i/j)*100) where jdmc=:new.jdmc and khyf=:new.khyf; else if (:old.wcjdj=0 and :new.wcjdj=1) then update t_jdl set wlrycjl= floor(((i+1)/j)*100) where jdmc=:new.jdmc and khyf=:new.khyf; else if (:old.wcjdj=1 and :new.wcjdj=0) then update t_jdl set wlrycjl= floor(((i-1)/j)*100) where jdmc=:new.jdmc and khyf=:new.khyf; end if; end if; end if; end if; end if; end if; exception when others then update t_jdl set wlrycjl= '0'; end tri_t_wlrycjl;
before insert ON a
FOR EACH ROW
declare
vcount INTEGER;
BEGIN
select count(*) into vcount from a;
update b set id=(id*vcount+:new.id )/(vcount+1);
END;
其中有三个表(t_wlrycjl,t_wlrycjl_bak,t_jdl)
create or replace trigger tri_wlrycjl
after
insert or delete or update
on t_wlrycjl
for each row
declare
i int;
j int;
begin
if inserting then
select count(wcjdj) into i from t_jark_wlrycjl_bak where wcjdj='0' and khyf=:new.khyf and jdmc=:new.jdmc;
select count(wcjdj) into j from t_jark_wlrycjl_bak where khyf=:new.khyf and jdmc=:new.jdmc;
insert into t_wlrycjl_bak values(:new.JDMC,:new.KHYF,:new.XLH,:new.WLRXM,:new.ZJHM,:new.KHRQ,:new.RZRQ,:new.ZZZDZGX,:new.ZZZDJD,:new.FWDJZZCJ,:new.JWH,:new.YLSJZZ,:new.WCJDJ,:new.XJZDZ ,:new.CJRJH,:new.CJSJ );
if :new.wcjdj='0' then
update t_jdl set wlrycjl=floor((j-i)/(j+1)*100) where khyf=:new.khyf and jdmc=:new.jdmc;
else
update t_jdl set wlrycjl=floor((j-i+1)/(j+1)*100) where khyf=:new.khyf and jdmc=:new.jdmc;
end if;
else if deleting then
select count(wcjdj) into i from t_wlrycjl_bak where wcjdj='0' and khyf=:old.khyf and jdmc=:old.jdmc;
select count(wcjdj) into j from t_wlrycjl_bak where khyf=:old.khyf and jdmc=:old.jdmc;
delete from t_wlrycjl_bak where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
if :old.wcjdj='0' then
update t_jdl set wlrycjl=floor((j-i)/(j-1)*100) where khyf=:old.khyf and jdmc=:old.jdmc;
else
update t_jdl set wlrycjl=floor((j-i-1)/(j-1)*100) where khyf=:old.khyf and jdmc=:old.jdmc;
end if;
else if updating then
select count(wcjdj) into i from t_wlrycjl_bak where wcjdj='0' and khyf=:new.khyf and jdmc=:new.jdmc;
select count(wcjdj) into j from t_wlrycjl_bak where khyf=:new.khyf and jdmc=:new.jdmc;
update t_wlrycjl_bak set JDMC=:new.JDMC where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set KHYF=:new.KHYF where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set XLH=:new.XLH where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set ZJHM=:new.ZJHM where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set KHRQ=:new.KHRQ where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set RZRQ =:new.RZRQ where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set ZZZDZGX=:new.ZZZDZGX where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set ZZZDJD=:new.ZZZDJD where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set FWDJZZCJ=:new.FWDJZZCJ where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set JWH =:new.JWH where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set YLSJZZ=:new.YLSJZZ where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set WCJDJ=:new.WCJDJ where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set XJZDZ=:new.XJZDZ where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set CJRJH=:new.CJRJH where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
update t_wlrycjl_bak set CJSJ=:new.CJSJ where wlrxm=:old.wlrxm and zjhm=:old.zjhm;
if (:old.wcjdj=0 and :new.wcjdj=0) or (:old.wcjdj=1 and :new.wcjdj=1) then
update t_jdl set wlrycjl= floor((i/j)*100) where jdmc=:new.jdmc and khyf=:new.khyf;
else if (:old.wcjdj=0 and :new.wcjdj=1) then
update t_jdl set wlrycjl= floor(((i+1)/j)*100) where jdmc=:new.jdmc and khyf=:new.khyf;
else if (:old.wcjdj=1 and :new.wcjdj=0) then
update t_jdl set wlrycjl= floor(((i-1)/j)*100) where jdmc=:new.jdmc and khyf=:new.khyf;
end if;
end if;
end if;
end if;
end if;
end if;
exception
when others then
update t_jdl set wlrycjl= '0';
end tri_t_wlrycjl;