ES10_MobileAuthor双向同步触发器分享

grant select,update,delete,insert on bi.ES10_MobileAuthor to bi2;

grant select,update,delete,insert on bi2.ES10_MobileAuthor to bi;
--创建包头
create or replace package pk_check_active is
  --标识是否为触发器引发
  n number :=0;
  --获取是否为触发器触发标识，1为触发器触发
  function getactive return number;
  --设置状态
  procedure setactive(n1 in number);
end pk_check_active;

create or replace package body pk_check_active is
  function getactive return number is
  begin
    return n;
  end getactive;
  procedure setactive(n1 in number) is
  begin
    n := n1;
  end setactive;
end pk_check_active;



---bi1触发器
create or replace trigger tres10_mobileauthor after insert or update or delete
on es10_mobileauthor for each row
begin
   if pk_check_active.getactive() =1 then
     pk_check_active.setactive(2);  return;
    else
     pk_check_active.setactive(1);
   end if;
if inserting then
  insert into bi2.ES10_MobileAuthor(UUID_, UNAME_, REGISTERTIME_, ISAGREE_, DESC_, ISJY_) values (:new.UUID_, :new.UNAME_, :new.REGISTERTIME_, :new.ISAGREE_, :new.DESC_, :new.ISJY_);
elsif updating and :new.ISAGREE_ <> ld.ISAGREE_ then         
  update bi2.ES10_MobileAuthor set  ISAGREE_ = :new.ISAGREE_ where UUID_ = :new.UUID_;
elsif updating and :new.ISJY_ <> ld.ISJY_ then
  update bi2.ES10_MobileAuthor set  ISJY_ = :new.ISJY_ where UUID_ = :new.UUID_;
elsif deleting then
  delete from bi2.ES10_MobileAuthor where UUID_ = ld.UUID_;
end if;
    pk_check_active.setactive(0);
end;

--将包授权
grant execute on PK_CHECK_ACTIVE to bi2;

--bi2中建立包同义词
create synonym PK_CHECK_ACTIVE for bi.PK_CHECK_ACTIVE;



--bi2触发器
create or replace trigger tres10_mobileauthor after insert or update or delete
on es10_mobileauthor for each row
begin
   if pk_check_active.getactive() =1 then
     pk_check_active.setactive(2);  return;
    else
     pk_check_active.setactive(1);
   end if;
if inserting then
  insert into bi.ES10_MobileAuthor(UUID_, UNAME_, REGISTERTIME_, ISAGREE_, DESC_, ISJY_) values (:new.UUID_, :new.UNAME_, :new.REGISTERTIME_, :new.ISAGREE_, :new.DESC_, :new.ISJY_);
elsif updating and :new.ISAGREE_ <> ld.ISAGREE_ then         
  update bi.ES10_MobileAuthor set  ISAGREE_ = :new.ISAGREE_ where UUIeD_ = :new.UUID_;
elsif updating and :new.ISJY_ <> ld.ISJY_ then
  update bi.ES10_MobileAuthor set  ISJY_ = :new.ISJY_ where UUID_ = :new.UUID_;
elsif deleting then
  delete from bi.ES10_MobileAuthor where UUID_ = ld.UUID_;
end if;
    pk_check_active.setactive(0);
end;