CREATE OR REPLACE TRIGGER TR_YS_CRBDJ0_TB
 AFTER 
 INSERT OR DELETE OR UPDATE
 ON SD_MZ.YS_CRBDJ0
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
declare
  V_COUNT0       number(5,0)          ;--临时变量
  V_GHH000       number(12)           ;--挂号号
  V_GHH005       char(12)             ;--5.0数据库 挂号号
  V_GHID05       number(10)           ;--5.0数据库 门诊病人挂号信息流水号
-- MODIFICATION HISTORY
-- Person       Date        Comments
-- chenna       2021-06-16  Create:9.0数据库传染病登记增删改时同步到5.0数据库 MZYS9-20210603-001  归档
-- ---------    ----------  -------------------------------------------
begin
  --9.0与5.0数据GHID00不同，但GHH000相同。根据GHH000查询到5.0对应的GHID00在同步数据
  begin
    select GHH000 into V_GHH000 from SF_BRXXB0 where GHID00=:new.MZID00;
    V_GHH005:= rpad(V_GHH000,12)  ;
  exception
    when others then
       RAISE_APPLICATION_ERROR(-20903, '发生错误！*GHH000查询到SF_BRXXB0');
      return;
  end;
   begin
    select count(*) into V_COUNT0 from SF_BRXXB0@his_dblink where GHH000=V_GHH005;
    if V_COUNT0 >0 then
      select GHID00 into V_GHID05 from SF_BRXXB0@his_dblink where GHH000=V_GHH005;
    else
       RAISE_APPLICATION_ERROR(-20904, 'GHH000查询'|| V_GHH005||'weikong');
      return;
    end if;
  exception
    when others then
       RAISE_APPLICATION_ERROR(-20905, '*GHH000查询到SF_BRXXB0@his_dblink'||SQLERRM);
      return;
  end;
  if :new.DJID00 is not null then
    if inserting then
      insert into YS_CRBDJ0@his_dblink (DJID00, MZID00, HZXM00, BRXB00, BRCSRQ, SFZH00, TYDWMC, BRLXDH, DZSF00, DZDJS0, DZXQ00,
        DZXZJD, DZC000, DZMPH0, BRLXDZ, BRZY00, HEJZXM, BRLS00, FBRQ00, ZDRQ00, ZDSJ00, SWRQ00, BLFL01, BLFL02, TKYS00,
        TKRQ00, JBMC00, JCZCR0, BZ0000, CZY000, CZRQ00, CZSJ00, HY0000, WHCD00, CRLY00, SFSW00, SFQR00, ZDXXID, JLCRB0, YLCRB0, 
        BLCRB0, CRB001, CRB002, CRB003, CRB004, CRB005, CRB006, CRB007, CRB008, CRB009, CRB010, CRB011, CRB012, CRB013, CRB014, 
        CRB015, CRB016, CRB017, CRB018, CRB019, CRB020, CRB021, CRB022, CRB023, CRB024, CRB025, CRB026, CRB027, CRB028, CRB029, CRB030, 
        CRB031, CRB032, CRB033, CRB034, CRB035, CRB036, CRB037, CRB038, CRB039, CRB040, CRB041, CRB042, CRB043, CRB044, CRB045, CRB046, CRB047, 
        CRB048, CRB049, CRB050, CRB051, CRB052, CRB053, CRB054, CRB055, CRB056, CRB057, CRB058, CRB059, CRB060, KPBH00, KPLX00, QTCRB0, DZJBMC, 
        TKYY00, DZSZC0, TKSJ00, ZDYJ00, CRB061, CRB062, CRB063, BRID00, QRR000, CRB064, CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, 
        CRB072, BRSZNL, CRB073, CRB074, CRB075, CRB076, CRB077, CRB078, CRB079, CRB080, CRB081, CRB082, CRB083, DYCS00, BGRQ00, BGBZ00, BRLB00, 
        YJDJKS, CRB084, CRB085, CRB086, BGDWMC, BGDWDH, CRB087, CRB088, CRB089, CRB090, CRB091, CRB092, CRB093 )
      values (:new.DJID00, V_GHID05, :new.HZXM00, :new.BRXB00, :new.BRCSRQ, :new.SFZH00, :new.TYDWMC, :new.BRLXDH, :new.DZSF00, 
        :new.DZDJS0, :new.DZXQ00, :new.DZXZJD, :new.DZC000, :new.DZMPH0, :new.BRLXDZ, :new.BRZY00, :new.HEJZXM, substr(:new.BRLS00,2), :new.FBRQ00, 
        :new.ZDRQ00, :new.ZDSJ00, :new.SWRQ00, substr(:new.BLFL01,3), :new.BLFL02, :new.TKYS00, :new.TKRQ00, :new.JBMC00, :new.JCZCR0, :new.BZ0000, 
        :new.CZY000, :new.CZRQ00, :new.CZSJ00, :new.HY0000, :new.WHCD00, :new.CRLY00, :new.SFSW00, :new.SFQR00, :new.ZDXXID, :new.JLCRB0, 
        :new.YLCRB0, :new.BLCRB0, :new.CRB001, :new.CRB002, :new.CRB003, :new.CRB004, :new.CRB005, :new.CRB006, :new.CRB007, :new.CRB008, 
        :new.CRB009, :new.CRB010, :new.CRB011, :new.CRB012, :new.CRB013, :new.CRB014, :new.CRB015, :new.CRB016, :new.CRB017, :new.CRB018, 
        :new.CRB019, :new.CRB020, :new.CRB021, :new.CRB022, :new.CRB023, :new.CRB024, :new.CRB025, :new.CRB026, :new.CRB027, :new.CRB028, 
        :new.CRB029, :new.CRB030, :new.CRB031, :new.CRB032, :new.CRB033, :new.CRB034, :new.CRB035, :new.CRB036, :new.CRB037, :new.CRB038, 
        :new.CRB039, :new.CRB040, :new.CRB041, :new.CRB042, :new.CRB043, :new.CRB044, :new.CRB045, :new.CRB046, :new.CRB047, :new.CRB048, 
        :new.CRB049, :new.CRB050, :new.CRB051, :new.CRB052, :new.CRB053, :new.CRB054, :new.CRB055, :new.CRB056, :new.CRB057, :new.CRB058, 
        :new.CRB059, :new.CRB060, :new.KPBH00, :new.KPLX00, :new.QTCRB0, :new.DZJBMC, :new.TKYY00, :new.DZSZC0, :new.TKSJ00, :new.ZDYJ00, 
        :new.CRB061, :new.CRB062, :new.CRB063, :new.BRID00, :new.QRR000, :new.CRB064, :new.CRB065, :new.CRB066, :new.CRB067, :new.CRB068, 
        :new.CRB069, :new.CRB070, :new.CRB071, :new.CRB072, :new.BRSZNL, :new.CRB073, :new.CRB074, :new.CRB075, :new.CRB076, :new.CRB077, 
        :new.CRB078, :new.CRB079, :new.CRB080, :new.CRB081, :new.CRB082, :new.CRB083, :new.DYCS00, :new.BGRQ00, :new.BGBZ00, :new.BRLB00, 
        :new.YJDJKS, '', '', :new.CRB086, '' , '' , :new.CRB087, :new.CRB088, :new.CRB089, :new.CRB090, '', '', '' );
    elsif deleting then
      select count(*) into V_COUNT0 from YS_CRBDJ0@his_dblink where DJID00= :new.DJID00 and MZID00 = V_GHID05;
      if V_COUNT0 > 0 then
        delete from YS_CRBDJ0@his_dblink where DJID00= :new.DJID00 and MZID00 = V_GHID05;
      end if;
    elsif updating then
      select count(*) into V_COUNT0 from YS_CRBDJ0@his_dblink where DJID00= :new.DJID00;
      if V_COUNT0 > 0 then
        update YS_CRBDJ0@his_dblink set SFZH00 = :new.SFZH00, TYDWMC = :new.TYDWMC, BRLXDH = :new.BRLXDH, DZSF00 = :new.DZSF00, DZDJS0 = :new.DZDJS0, 
        DZXQ00 = :new.DZXQ00, DZXZJD = :new.DZXZJD, DZC000 = :new.DZC000, DZMPH0 = :new.DZMPH0, BRLXDZ = :new.BRLXDZ, BRZY00 = :new.BRZY00, 
        HEJZXM = :new.HEJZXM, BRLS00 = substr(:new.BRLS00,2), FBRQ00 = :new.FBRQ00, ZDRQ00 = :new.ZDRQ00, ZDSJ00 = :new.ZDSJ00, SWRQ00 = :new.SWRQ00, 
        BLFL01 = substr(:new.BLFL01,3), BLFL02 = :new.BLFL02, TKYS00 = :new.TKYS00, TKRQ00 = :new.TKRQ00, JBMC00 = :new.JBMC00, JCZCR0 = :new.JCZCR0, 
        BZ0000 = :new.BZ0000, CZY000 = :new.CZY000, CZRQ00 = :new.CZRQ00, CZSJ00 = :new.CZSJ00, HY0000 = :new.HY0000, WHCD00 = :new.WHCD00, 
        CRLY00 = :new.CRLY00, SFSW00 = :new.SFSW00, SFQR00 = :new.SFQR00, ZDXXID = :new.ZDXXID, JLCRB0 = :new.JLCRB0, YLCRB0 = :new.YLCRB0, 
        BLCRB0 = :new.BLCRB0, CRB001 = :new.CRB001, CRB002 = :new.CRB002, CRB003 = :new.CRB003, CRB004 = :new.CRB004, CRB005 = :new.CRB005, 
        CRB006 = :new.CRB006, CRB007 = :new.CRB007, CRB008 = :new.CRB008, CRB009 = :new.CRB009, CRB010 = :new.CRB010, CRB011 = :new.CRB011, 
        CRB012 = :new.CRB012, CRB013 = :new.CRB013, CRB014 = :new.CRB014, CRB015 = :new.CRB015, CRB016 = :new.CRB016, CRB017 = :new.CRB017, 
        CRB018 = :new.CRB018, CRB019 = :new.CRB019, CRB020 = :new.CRB020, CRB021 = :new.CRB021, CRB022 = :new.CRB022, CRB023 = :new.CRB023, 
        CRB024 = :new.CRB024, CRB025 = :new.CRB025, CRB026 = :new.CRB026, CRB027 = :new.CRB027, CRB028 = :new.CRB028, CRB029 = :new.CRB029, 
        CRB030 = :new.CRB030, CRB031 = :new.CRB031, CRB032 = :new.CRB032, CRB033 = :new.CRB033, CRB034 = :new.CRB034, CRB035 = :new.CRB035, 
        CRB036 = :new.CRB036, CRB037 = :new.CRB037, CRB038 = :new.CRB038, CRB039 = :new.CRB039, CRB040 = :new.CRB040, CRB041 = :new.CRB041, 
        CRB042 = :new.CRB042, CRB043 = :new.CRB043, CRB044 = :new.CRB044, CRB045 = :new.CRB045, CRB046 = :new.CRB046, CRB047 = :new.CRB047, 
        CRB048 = :new.CRB048, CRB049 = :new.CRB049, CRB050 = :new.CRB050, CRB051 = :new.CRB051, CRB052 = :new.CRB052, CRB053 = :new.CRB053,
        CRB054 = :new.CRB054, CRB055 = :new.CRB055, CRB056 = :new.CRB056, CRB057 = :new.CRB057, CRB058 = :new.CRB058, CRB059 = :new.CRB059, 
        CRB060 = :new.CRB060, KPBH00 = :new.KPBH00, KPLX00 = :new.KPLX00, QTCRB0 = :new.QTCRB0, DZJBMC = :new.DZJBMC, TKYY00 = :new.TKYY00, 
        DZSZC0 = :new.DZSZC0, TKSJ00 = :new.TKSJ00, ZDYJ00 = :new.ZDYJ00, CRB061 = :new.CRB061, CRB062 = :new.CRB062, CRB063 = :new.CRB063, 
        BRID00 = :new.BRID00, QRR000 = :new.QRR000, CRB064 = :new.CRB064, CRB065 = :new.CRB065, CRB066 = :new.CRB066, CRB067 = :new.CRB067, 
        CRB068 = :new.CRB068, CRB069 = :new.CRB069, CRB070 = :new.CRB070, CRB071 = :new.CRB071, CRB072 = :new.CRB072, BRSZNL = :new.BRSZNL, 
        CRB073 = :new.CRB073, CRB074 = :new.CRB074, CRB075 = :new.CRB075, CRB076 = :new.CRB076, CRB077 = :new.CRB077, CRB078 = :new.CRB078, 
        CRB079 = :new.CRB079, CRB080 = :new.CRB080, CRB081 = :new.CRB081, CRB082 = :new.CRB082, CRB083 = :new.CRB083, DYCS00 = :new.DYCS00,
        BGRQ00 = :new.BGRQ00, BGBZ00 = :new.BGBZ00, BRLB00 = :new.BRLB00, YJDJKS = :new.YJDJKS, CRB086 = :new.CRB086, CRB087 = :new.CRB087, 
        CRB088 = :new.CRB088, CRB089 = :new.CRB089, CRB090 = :new.CRB090, MZID00 = V_GHID05  where DJID00= :new.DJID00;
      else
        insert into YS_CRBDJ0@his_dblink (DJID00, MZID00, HZXM00, BRXB00, BRCSRQ, SFZH00, TYDWMC, BRLXDH, DZSF00, DZDJS0, DZXQ00,
        DZXZJD, DZC000, DZMPH0, BRLXDZ, BRZY00, HEJZXM, BRLS00, FBRQ00, ZDRQ00, ZDSJ00, SWRQ00, BLFL01, BLFL02, TKYS00,
        TKRQ00, JBMC00, JCZCR0, BZ0000, CZY000, CZRQ00, CZSJ00, HY0000, WHCD00, CRLY00, SFSW00, SFQR00, ZDXXID, JLCRB0, YLCRB0, 
        BLCRB0, CRB001, CRB002, CRB003, CRB004, CRB005, CRB006, CRB007, CRB008, CRB009, CRB010, CRB011, CRB012, CRB013, CRB014, 
        CRB015, CRB016, CRB017, CRB018, CRB019, CRB020, CRB021, CRB022, CRB023, CRB024, CRB025, CRB026, CRB027, CRB028, CRB029, CRB030, 
        CRB031, CRB032, CRB033, CRB034, CRB035, CRB036, CRB037, CRB038, CRB039, CRB040, CRB041, CRB042, CRB043, CRB044, CRB045, CRB046, CRB047, 
        CRB048, CRB049, CRB050, CRB051, CRB052, CRB053, CRB054, CRB055, CRB056, CRB057, CRB058, CRB059, CRB060, KPBH00, KPLX00, QTCRB0, DZJBMC, 
        TKYY00, DZSZC0, TKSJ00, ZDYJ00, CRB061, CRB062, CRB063, BRID00, QRR000, CRB064, CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, 
        CRB072, BRSZNL, CRB073, CRB074, CRB075, CRB076, CRB077, CRB078, CRB079, CRB080, CRB081, CRB082, CRB083, DYCS00, BGRQ00, BGBZ00, BRLB00, 
        YJDJKS, CRB084, CRB085, CRB086, BGDWMC, BGDWDH, CRB087, CRB088, CRB089, CRB090, CRB091, CRB092, CRB093 )
      values (:new.DJID00, V_GHID05, :new.HZXM00, :new.BRXB00, :new.BRCSRQ, :new.SFZH00, :new.TYDWMC, :new.BRLXDH, :new.DZSF00, 
        :new.DZDJS0, :new.DZXQ00, :new.DZXZJD, :new.DZC000, :new.DZMPH0, :new.BRLXDZ, :new.BRZY00, :new.HEJZXM, substr(:new.BRLS00,2), :new.FBRQ00, 
        :new.ZDRQ00, :new.ZDSJ00, :new.SWRQ00, substr(:new.BLFL01,3), :new.BLFL02, :new.TKYS00, :new.TKRQ00, :new.JBMC00, :new.JCZCR0, :new.BZ0000, 
        :new.CZY000, :new.CZRQ00, :new.CZSJ00, :new.HY0000, :new.WHCD00, :new.CRLY00, :new.SFSW00, :new.SFQR00, :new.ZDXXID, :new.JLCRB0, 
        :new.YLCRB0, :new.BLCRB0, :new.CRB001, :new.CRB002, :new.CRB003, :new.CRB004, :new.CRB005, :new.CRB006, :new.CRB007, :new.CRB008, 
        :new.CRB009, :new.CRB010, :new.CRB011, :new.CRB012, :new.CRB013, :new.CRB014, :new.CRB015, :new.CRB016, :new.CRB017, :new.CRB018, 
        :new.CRB019, :new.CRB020, :new.CRB021, :new.CRB022, :new.CRB023, :new.CRB024, :new.CRB025, :new.CRB026, :new.CRB027, :new.CRB028, 
        :new.CRB029, :new.CRB030, :new.CRB031, :new.CRB032, :new.CRB033, :new.CRB034, :new.CRB035, :new.CRB036, :new.CRB037, :new.CRB038, 
        :new.CRB039, :new.CRB040, :new.CRB041, :new.CRB042, :new.CRB043, :new.CRB044, :new.CRB045, :new.CRB046, :new.CRB047, :new.CRB048, 
        :new.CRB049, :new.CRB050, :new.CRB051, :new.CRB052, :new.CRB053, :new.CRB054, :new.CRB055, :new.CRB056, :new.CRB057, :new.CRB058, 
        :new.CRB059, :new.CRB060, :new.KPBH00, :new.KPLX00, :new.QTCRB0, :new.DZJBMC, :new.TKYY00, :new.DZSZC0, :new.TKSJ00, :new.ZDYJ00, 
        :new.CRB061, :new.CRB062, :new.CRB063, :new.BRID00, :new.QRR000, :new.CRB064, :new.CRB065, :new.CRB066, :new.CRB067, :new.CRB068, 
        :new.CRB069, :new.CRB070, :new.CRB071, :new.CRB072, :new.BRSZNL, :new.CRB073, :new.CRB074, :new.CRB075, :new.CRB076, :new.CRB077, 
        :new.CRB078, :new.CRB079, :new.CRB080, :new.CRB081, :new.CRB082, :new.CRB083, :new.DYCS00, :new.BGRQ00, :new.BGBZ00, :new.BRLB00, 
        :new.YJDJKS, '', '', :new.CRB086, '' , '' , :new.CRB087, :new.CRB088, :new.CRB089, :new.CRB090, '', '', '' );
      end if;
    end if;
  else
     RAISE_APPLICATION_ERROR(-20904, 'new.djid00 is null');
  end if;
  
exception
  when others then
    RAISE_APPLICATION_ERROR(-20904, 'YS_CRBDJ0@his_dblink时发生错误！*'||TO_CHAR(SQLCODE)||SQLERRM);
end;
/