触发器增删改

-- 增加部门时，且READ_PLM为0触发sys_015增加
create or replace trigger hr_015_insert
before insert on hr_tuo2
for each row
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   sour number:=0;
   parenid varchar2(20);
  begin
    :new.dept_id_copy := :new.dept_id;
    parenid:=:new.parent_id;
    select count(cgroupid) into sour from sys_015@dblink where cgroupid=:new.dept_id_copy;
    if :new.READ_PLM=0 and sour=0 then
       :new.READ_PLM:=1;
       insert into sys_015@dblink(CGROUPID, CGROUPNAME, CPARENTGROUPID, groupcode) values(:new.dept_id_copy,:new.DESCRIPTION, :new.PARENT_ID,:new.DEPT_ID);
    end if;
    if :new.READ_PLM=0 and sour!=0 then
       :new.READ_PLM:=1;
       update sys_015@dblink set CPARENTGROUPID=parenid where cgroupid=:new.dept_id_copy;
       update sys_015@dblink set CGROUPNAME=:new.DESCRIPTION where cgroupid=:new.dept_id_copy;
    end if;
    commit;
  end hr_015_insert;
-- 修改部门表的时候添加触发器，触发sys_015修改相应的部分
create or replace trigger hr_015_tri
before update on hr_tuo2
for each row
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   sour varchar2(20);
   medept varchar2(20);
  begin
    :new.dept_id_copy := :old.dept_id;
    sour:=:new.parent_id;
     if :old.parent_id<>:new.parent_id then
       update sys_015@dblink set CPARENTGROUPID=sour where cgroupid=:old.dept_id_copy;
     end if;
     if :old.description<>:new.description then
       update sys_015@dblink set cgroupname=:new.description where cgroupid=:old.dept_id_copy;
     end if;
     if :new.deletesign=1 and :new.READ_PLM=1 then
       delete sys_015@dblink where cgroupid=:old.dept_id_copy;
     end if;
    commit;
end hr_015_tri;
--导入部门
create or replace procedure huitian0152 as
  begin
    for x in (select id, DESCRIPTION, PARENT_ID, DEPT_ID from HR_TUo2 where  (READ_PLM=0 or READ_PLM is null)and dept_id in (
        select s.dept_id 
         from ( 
         select a.ID,a.dept_id,row_number() over (partition by a.dept_id order by a.ID desc) as rn 
         from HR_TUo2 a where a.deletesign=0
          ) s where s.rn=1
      )) loop
      insert into SYS_015@dblink
        (CGROUPID, CGROUPNAME, CPARENTGROUPID, groupcode)
      values
        (x.id, x.DESCRIPTION, x.PARENT_ID, x.DEPT_ID);
    end loop;
    commit;
  end;

 --增加人员 
create or replace trigger hr_019_insert
before insert on hr_tue01
for each row
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   sour varchar2(20);
   emal varchar2(100);
   mm number:=0;
   hh number:=0;
   a number:=0;
   b number:=1;
  begin
    :new.id_copy:=:new.id;
    :new.id_copy1:=:new.id;
    if :new.READ_PLM=0 then
       :new.READ_PLM:=1;
       select count(*) into mm from sys_019@dblink where CPERSONID=:new.id_copy1;
       emal:=CONCAT(:new.PERS_ID,'C:\PLM_WORKDIR');
       if mm=0 then
         insert into sys_019@dblink(CPERSONID,CPERSONNAME,CPERSONPW,CPERSONWP,CPERKEYID,CPERSONEMAIL,CDETAIL,CVIP,CDELETED,CGENDER,CISMULTILOGIN,CCREATETIME)
         values(:new.ID,:new.NAME,'B59C67BF196A4758191E42F76670CEBA','C:\PLM_WORKDIR',:new.PERS_ID,emal,:new.PERS_ID,a,a,b,a,:new.ts);
         select s.dept_id into sour
           from (
           select a.ID,a.dept_id,row_number() over (partition by a.dept_id order by a.ID desc) as rn
           from HR_TUo2 a where a.seq_no=:new.dept_code
            ) s where s.rn=1;
         insert into sys_020@dblink(CPERSONID,CGROUPID,CACCESSDATE) values(:new.ID,sour,:new.ts);
       end if;
       if mm!=0 then
         update sys_019@dblink set CPERSONNAME=:new.NAME where CPERSONID=:new.id_copy1;
         update sys_019@dblink set CDELETED=0 where CPERSONID=:new.id_copy;
         select s.dept_id into sour
           from (
           select a.ID,a.dept_id,row_number() over (partition by a.dept_id order by a.ID desc) as rn
           from HR_TUo2 a where a.seq_no=:new.dept_code
            ) s where s.rn=1;
         select count(*) into hh from sys_020@dblink where CPERSONID=:new.id;
         if hh=0 then
            insert into sys_020@dblink(CPERSONID,CGROUPID,CACCESSDATE) values(:new.ID,sour,:new.ts);
         end if;
         if hh!=0 then
           update sys_020@dblink set CGROUPID=sour where CPERSONID=:new.id_copy;
           end if;
       end if;
    end if;
    commit;
  end hr_019_insert;
  
-- 修改用户名字和部门，触发sys_019中name和sys_020中cgroupid的修改
-- 因为每一个部门的seq_no不一样，修改部门之后会自动修改
create or replace trigger hr_019_tri
before update on hr_tue01
for each row 
  DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION; 
  begin
      :new.id_copy :=:old.id;
     if :old.dept_code<>:new.dept_code then 
       select s.dept_id into :new.dept_id_copy from ( 
         select a.ID,a.dept_id,row_number() over (partition by a.dept_id order by a.ID desc) as rn 
         from HR_TUo2 a where a.seq_no=:new.dept_code
          ) s where s.rn=1; 
       update sys_020@dblink set cgroupid=:new.dept_id_copy where cpersonid=:new.id_copy;
     end if;
     if :old.name<>:new.name then 
       :new.id_copy1 := :old.id;
       update sys_019@dblink set cpersonname=:new.name where cpersonid=:new.id_copy1;
     end if;
      if  
    commit;
  end hr_019_tri;

--删除
create or replace trigger hr_019_delete
before delete on hr_tue01
for each row
 begin
    update sys_019@dblink set CDELETED=1 where cpersonid=:old.id_copy1;
    delete sys_020@dblink where cpersonid=:old.id_copy;
  end hr_019_delete;
  
  
  --人员表数据转移
  
create or replace procedure huitian019 as
  begin
    for x in (select ID, NAME,'B59C67BF196A4758191E42F76670CEBA' as xx ,'C:\PLM_WORKDIR' as c,PERS_ID,
      CONCAT(PERS_ID, '@plm.com') as con,'0' as a,'0' as b,'1' as d,'0' as e ,sysdate as f from HR_TUE01 where (READ_PLM=0 or READ_PLM is null) and perstype='在职') loop
      insert into SYS_019@dblink
        (CPERSONID,CPERSONNAME,CPERSONPW,CPERSONWP,CPERKEYID,CPERSONEMAIL,CVIP,CDELETED,CGENDER,CISMULTILOGIN,CCREATETIME)
      values
        (x.ID, x.NAME,x.xx,x.c,x.PERS_ID,x.con,x.a,x.b,x.d,x.e,x.f);
    end loop;
    commit;
  end;