trigger & procedure

 

 

t_hes_manage_relation

         |

         |after insert or
         | update of tree_id,secretary,manager,use_flag
         | or delete
         | on t_hes_manage_relation 
         | for each row

         |

tg_secretary_exchange_store.trg

         |

         |调用存储过程

         |

adjust_temp_relation.prc

t_hes_manage_temp（建立人员如（秘书，经理）与分行的联系）

 

create or replace trigger tg_secretary_exchange_store
  after insert or
  update of tree_id,secretary,manager,use_flag
  or delete
  on t_hes_manage_relation 
  for each row
--建立人（秘书,区域经理，分行经理，业务经理）与分行之间的联系
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  -- local variables here
begin

    if inserting then
--在关系中加上（秘书,区域经理，分行经理，业务经理）
   /*
             调整 :new.manager  的管理关系对应表
             调整 :new.secretary  的管理关系对应表
      */
      adjust_temp_relation(:new.tree_id,:new.manager,1);
      adjust_temp_relation(:new.tree_id,:new.secretary,1);

--在关系中对（秘书,区域经理，分行经理，业务经理）进行（修改，不启用）操作
 elsif updating then
   /*
          if :new.manager ！= :old.manager then
             调整 :old.manager  的管理关系对应表
             调整 :new.manager  的管理关系对应表
          else
              if :new.use_flag ！= :old.use_flag then
                 调整 :new.manager  的管理关系对应表
              end if;
          end if;
     
        if :new.secretary ！= :old.:secretary then
          调整 :old.secretary  的管理关系对应表
          调整 :new.secretary  的管理关系对应表
          else
              if :new.use_flag ！= :old.use_flag then
                 调整 :new.secretary  的管理关系对应表
              end if;
         end if;
      */
          if :new.manager <> :old.manager then
            adjust_temp_relation(:old.tree_id,:old.manager,0);
            adjust_temp_relation(:new.tree_id,:new.manager,1);
          else
              if :new.tree_id <> :old.tree_id then
                 adjust_temp_relation(:old.tree_id,:old.manager,0);
                adjust_temp_relation(:new.tree_id,:new.manager,1);
              end if;
              if :new.use_flag <> :old.use_flag then
                adjust_temp_relation(:new.tree_id,:new.manager,:new.use_flag);
              end if;
          end if;
     
       if :new.secretary <> :old.secretary then
              adjust_temp_relation(:old.tree_id,:old.secretary,0);
              adjust_temp_relation(:new.tree_id,:new.secretary,1);
          else
              if :new.tree_id <> :old.tree_id then
                adjust_temp_relation(:old.tree_id,:old.secretary,0);
                adjust_temp_relation(:new.tree_id,:new.secretary,1);
              end if;
              if :new.use_flag <> :old.use_flag then
                  adjust_temp_relation(:new.tree_id,:new.secretary,:new.use_flag);
              end if;
         end if;

  elsif deleting then
   /*
             调整 :old.manager  的管理关系对应表
             调整 :old.secretary  的管理关系对应表
      */
      adjust_temp_relation(:old.tree_id,:old.manager,0);
      adjust_temp_relation(:old.tree_id,:old.secretary,0);
    end if;
   
    commit;
exception
 when no_data_found then
  raise_application_error(-20999, '触发器信息(tg_secretary_exchange_store)发生异常，请检查 !');
end tg_secretary_exchange_store;
/

 

create or replace procedure adjust_temp_relation(tree_id1 in varchar,secretary1 in varchar,flag1 in number) is
begin
  if length(trim(nvl(secretary1,''))) >0 then
      delete from T_HES_MANAGE_TEMP t
      where t.user_id= secretary1
      and t.store_record_id in (select record_id from t_hes_manage_relation where tree_id like tree_id1||'%');
     
       if flag1 = 1 then
        insert into T_HES_MANAGE_TEMP t
        select record_id ,secretary1,flag1 from t_hes_manage_relation where tree_id like tree_id1||'%';
      end if;
  end if;
end adjust_temp_relation;