触发器实例2--涉及事务提交

1、声明自由事务

declare pragma autonomous_transaction;

2、结尾需执行COMMIT操作，提交事务。

 

create or replace trigger tri_usr_mstr_sync after insert or update or delete on usr_mstr
for each row
declare pragma autonomous_transaction;
  /*
  同步至惠南、科技系统
  */
begin
  --return;
   if deleting then
    delete from huinan.usr_mstr@huinanerp
    where usr_user = :old.usr_user;

    delete from usr_mstr@filedata
    where usr_user = :old.usr_user;

    delete from usrp_det
    where usrp_user = :old.usr_user;
  end if;

  if inserting then
    insert into huinan.usr_mstr@huinanerp
    (usr_user,usr_name,usr_group,usr_dept,usr_password,usr_def_site,usr_view_sites,usr_allow_ip,usr_bqq,usr_crt_by)
    values
    (:new.usr_user,:new.usr_name,:new.usr_group,:new.usr_dept,:new.usr_password,'1000','1000,1001,1002',:new.usr_allow_ip,:new.usr_bqq,
    :new.usr_crt_by);

    insert into usr_mstr@filedata
    (usr_user,usr_name,usr_group,usr_dept,usr_password,usr_def_site,usr_view_sites,usr_allow_ip,usr_bqq,usr_crt_by,usr_crt_date)
    values
    (:new.usr_user,:new.usr_name,:new.usr_group,:new.usr_dept,:new.usr_password,'1000','1000',:new.usr_allow_ip,:new.usr_bqq,
    :new.usr_crt_by,sysdate);
  end if;

  if updating then
    update huinan.usr_mstr@huinanerp set
           usr_name  = :new.usr_name,
           usr_dept  = :new.usr_dept,
           usr_password = :new.usr_password,
           usr_allow_ip = :new.usr_allow_ip,
           usr_bqq  = :new.usr_bqq,
           usr_group = :new.usr_group,
           usr_employee = :new.usr_employee,
           usr_lock = :new.usr_lock
    where 1=1 and usr_user = :new.usr_user;

    update usr_mstr@filedata set
           usr_name  = :new.usr_name,
           --usr_dept  = :new.usr_dept,
           usr_password = :new.usr_password,
           usr_allow_ip = :new.usr_allow_ip,
           usr_bqq  = :new.usr_bqq,
           usr_employee = :new.usr_employee,
           usr_lock = :new.usr_lock
    where 1=1 and usr_user = :new.usr_user;
  end if;

  commit;
end tri_usr_mstr_sync;