触发器实例(二)

create or replace trigger TR_T_DWXX
after delete or update or insert ON T_DWXX
 for each row
--单位 改变后 修改单位关系表
declare
  curr_dwdh number:=0 ;
  PRAGMA AUTONOMOUS_TRANSACTION;--开启自治事物
  i number:=0;
BEGIN

  if inserting then
     select t.n_sjdw into curr_dwdh from t_dwxx t where t.n_dwdh=:new.N_SJDW;
     loop -- 循环语句，找出当前节点上面还有几层节点,即分析当前节点是第几级
         exit when curr_dwdh =99999999;--根节点就退出

         INSERT INTO T_DWGX (N_DWDH,N_XJDW,N_GXJS)
          values (curr_dwdh,:new.N_DWDH,i);
          i:=i+1;
         select t.n_sjdw into curr_dwdh from t_dwxx t where   t.n_dwdh=curr_dwdh;
     end loop;
     INSERT INTO T_DWGX (N_DWDH,N_XJDW,N_GXJS)
      values (:new.N_SJDW,:new.N_DWDH,i);
  elsif updating then
    delete from T_DWGX where N_XJDW=:old.n_dwdh;
    select t.n_sjdw into curr_dwdh from t_dwxx t where t.n_dwdh=:new.N_SJDW;
    loop --循环语句，找出当前节点上面还有几层节点,即分析当前节点是第几级
         exit when curr_dwdh =99999999;--根节点就退出
          INSERT INTO T_DWGX (N_DWDH,N_XJDW,N_GXJS)
          values (curr_dwdh,:new.N_DWDH,i);
           i:=i+1;
         select t.n_sjdw into curr_dwdh from t_dwxx t where   t.n_dwdh=curr_dwdh;
    end loop;
    INSERT INTO T_DWGX (N_DWDH,N_XJDW,N_GXJS)
      values (:new.N_SJDW,:new.N_DWDH,i);
  elsif deleting then
     delete from T_DWGX where N_XJDW=:old.n_dwdh;
  end if;
COMMIT;
END;

 

转载于:https://www.cnblogs.com/mingyue1818/p/3327167.html