CREATE OR REPLACE TRIGGER TR_SF_YSGHHB_ALL
 AFTER 
 INSERT OR DELETE OR UPDATE OF GHJSSJ, GHXHS0, YYXHS0, GHYSBH, GHHZCK, GHMZSJ, GHKSBH, GHLBMC, SFZJGH, GHZKBH, GHYGHS, TZBZ00, GHYYHS, GHQSSJ
 ON SD_SF.SF_YSGHHB
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
declare
  -- local variables here
  VS_PROCESS XT_XTRZ00.JCID00%Type;
  VS_YGBH00 XT_XTRZ00.YGBH00%Type;
  VD_CZID00 Number;
  VS_CZNR00 XT_XTCZRZ.CZNR00%Type;
  VS_KSMC00 BM_BMBM00.BMMC00%Type;
  VS_ZKMC00 BM_ZKBM00.ZKMC00%Type;
  VS_YSMC00 BM_YGBM00.ZWXM00%Type;
  VS_CKMC00 BM_CKBM00.CKMC00%Type;
  LS_SFJYBXTPP varchar2(10);
  Verrmsg   varchar2(100);
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhr      2007.11.07     sf_ysghhb医生挂号号表　用户部门权限表记录操作日志触发器写法错误，变量使用错误，Verrmsg-->VS_CZNR00 导致产生的日志不知道是属于哪种操作．
-- CSF      2009.05.18     由于按科室排班时候，不知道具体医生，所以医生信息可能为空。
-- qks      2010.02.21     修改：TO_CHAR(:OLD.YYXHMX)在8i数据库中不支持
--laijg     2011.06.13     增加sf_ysghhb医生挂号号表开诊操作日志
--daihq    2012.09.12      增加停诊、加号操作日志 YYGH-20120507-001
--dsm      2016.03.25      当MZHS_SFJYBXTPP参数起用时,屏蔽此触发器 for MZHS-20160321-002
begin
  Verrmsg:='?';
--  If DELETING OR UPDATING OR INSERTING
  begin
    select substr(trim(VALUE0),1,1) into LS_SFJYBXTPP from XT_XTCS00 where name00='MZHS_SFJYBXTPP' ;
  exception
    when others then
        LS_SFJYBXTPP:='N'  ;
  end;  
  if LS_SFJYBXTPP='Y' then
     return;
  end if;   
  if INSERTING then
     VS_CZNR00:='insert:';
     select userenv('SESSIONID') PROCESS into VS_PROCESS from dual where rownum=1;
     Verrmsg:='select YGBH00 from XT_XTRZ00 where JCID00='||VS_PROCESS;
     begin
       select YGBH00 into VS_YGBH00 from XT_XTRZ00 where JCID00=VS_PROCESS and
              LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS);
     exception
       when others then
         VS_YGBH00:=0;
     end;
     select SQ_XT_XTCZRZ_ID0000.NEXTVAL into VD_CZID00 from dual where ROWNUM=1;

     if :NEW.GHKSBH is not null then
        select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00=:NEW.GHKSBH and ROWNUM=1;
     end if;
     if :NEW.GHZKBH is not null then
        begin
          select ZKMC00 into VS_ZKMC00 from BM_ZKBM00 where ZKBM00=:NEW.GHZKBH and ROWNUM=1;
        exception
          when OTHERS then
            VS_ZKMC00:='未知';
        end;
     end if;
     if :NEW.GHYSBH is not null then
       begin
         Select ZWXM00 into VS_YSMC00 from BM_YGBM00 where YGBH00=:NEW.GHYSBH and ROWNUM=1;
       exception
         when others then
           VS_YSMC00:='未知';
       end;
     end if;
     if :NEW.GHHZCK is not null then
       begin
         select CKMC00 into VS_CKMC00 from BM_CKBM00 where CKBH00=:NEW.GHHZCK and ROWNUM=1;
       exception
         when OTHERS then
           VS_CKMC00:='未知';
       end;
     end if;

     VS_CZNR00 := VS_CZNR00||'科室:'||trim((VS_KSMC00))||';';
     VS_CZNR00 := VS_CZNR00||'医生:'||trim((VS_YSMC00))||';';
     VS_CZNR00 := VS_CZNR00||'挂号类别:'||trim((:NEW.GHLBMC))||';';
     VS_CZNR00 := VS_CZNR00||'地点:'||trim((VS_CKMC00))||';';
     VS_CZNR00 := VS_CZNR00||'专科:'||trim((VS_ZKMC00))||';';
     VS_CZNR00 := VS_CZNR00||'门诊时间:'||trim(:NEW.GHMZSJ)||';';
     VS_CZNR00 := VS_CZNR00||'起始时间:'||trim(:NEW.GHQSSJ)||';';
     VS_CZNR00 := VS_CZNR00||'截止时间:'||trim(:NEW.GHJSSJ)||';';
     VS_CZNR00 := VS_CZNR00||'挂号限号:'||trim(to_char(:NEW.GHXHS0))||';';
     VS_CZNR00 := VS_CZNR00||'预约限号:'||trim(to_char(:NEW.YYXHS0))||';';
     VS_CZNR00 := VS_CZNR00||'已挂号:'||trim(to_char(:NEW.GHYGHS))||';';
     VS_CZNR00 := VS_CZNR00||'已预约号:'||trim(to_char(:NEW.GHYYHS))||';';
     VS_CZNR00 := VS_CZNR00||'排班类型:'||trim(to_char(:NEW.PBLX00))||';';

     insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
            values(VD_CZID00,'SF_YSGHHB',0,VS_YGBH00,VS_CZNR00,:NEW.GHHBID);
            --3开诊 4停诊
--            SELECT SQ_XT_XTCZRZ_ID0000.NEXTVAL INTO VD_CZID00 FROM DUAL WHERE ROWNUM=1;
--                 Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
--            Values(VD_CZID00,'SF_YSGHHB',3,VS_YGBH00,'开诊:医生:'||Trim((VS_YSMC00))||';门诊时间:'||Trim(:NEW.GHMZSJ),:NEW.GHHBID);
  elsif DELETING then
     VS_CZNR00:='delete:';
     select userenv('SESSIONID') PROCESS into VS_PROCESS from dual where rownum=1;
     Verrmsg:='select yGBH00 from XT_XTRZ00 where JCID00='||VS_PROCESS;
     begin
       select YGBH00 into VS_YGBH00 from XT_XTRZ00 where JCID00=VS_PROCESS and
              LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS);
     exception
       when others then
         VS_YGBH00:=0;
     end;
     select SQ_XT_XTCZRZ_ID0000.NEXTVAL into VD_CZID00 from dual where ROWNUM=1;

     if (:OLD.GHKSBH is not null)  then
        select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00=:OLD.GHKSBH and ROWNUM=1;
     end if;
     if (:OLD.GHZKBH Is Not Null) then
        begin
          select ZKMC00 into VS_ZKMC00 from BM_ZKBM00 where ZKBM00=:OLD.GHZKBH and ROWNUM=1;
        exception
          when OTHERS then
            VS_ZKMC00:='未知';
        end;
     end if;
     if (:OLD.GHYSBH is not null) then
        begin
          select ZWXM00 into VS_YSMC00 from BM_YGBM00 where YGBH00=:OLD.GHYSBH and ROWNUM=1;
        exception
          when others then
            VS_YSMC00:='未知';
        end;
     end if;
    if :OLD.GHHZCK is not null then
       begin
         select CKMC00 into VS_CKMC00 from BM_CKBM00 where CKBH00=:OLD.GHHZCK and ROWNUM=1;
       exception
         when others then
            VS_CKMC00:='未知';
       end;
     end if;

     VS_CZNR00 := VS_CZNR00||'科室:'||trim((VS_KSMC00))||';';
     VS_CZNR00 := VS_CZNR00||'医生:'||trim((VS_YSMC00))||';';
     VS_CZNR00 := VS_CZNR00||'挂号类别:'||trim((:OLD.GHLBMC))||';';
     VS_CZNR00 := VS_CZNR00||'地点:'||trim((VS_CKMC00))||';';
     VS_CZNR00 := VS_CZNR00||'专科:'||trim((VS_ZKMC00))||';';
     VS_CZNR00 := VS_CZNR00||'门诊时间:'||trim(:OLD.GHMZSJ)||';';
     VS_CZNR00 := VS_CZNR00||'起始时间:'||trim(:OLD.GHQSSJ)||';';
     VS_CZNR00 := VS_CZNR00||'截止时间:'||trim(:OLD.GHJSSJ)||';';
     VS_CZNR00 := VS_CZNR00||'挂号限号:'||trim(to_char(:OLD.GHXHS0))||';';
     VS_CZNR00 := VS_CZNR00||'预约限号:'||trim(to_char(:OLD.YYXHS0))||';';
     VS_CZNR00 := VS_CZNR00||'已挂号:'||trim(to_char(:OLD.GHYGHS))||';';
     VS_CZNR00 := VS_CZNR00||'已预约号:'||trim(to_char(:OLD.GHYYHS))||';';
     VS_CZNR00 := VS_CZNR00||'排班类型:'||trim(to_char(:OLD.PBLX00))||';';
     insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
            values(VD_CZID00,'SF_YSGHHB',2,VS_YGBH00,VS_CZNR00,:OLD.GHHBID);
  elsif UPDATING then
     VS_CZNR00:='update:';
     if (:OLD.GHKSBH<>:NEW.GHKSBH)or(:OLD.GHZKBH<>:NEW.GHZKBH)or(:OLD.GHYSBH<>:NEW.GHYSBH)
        or(:OLD.GHLBMC<>:NEW.GHLBMC)or(:OLD.GHMZSJ<>:NEW.GHMZSJ)or(:OLD.GHQSSJ<>:NEW.GHQSSJ)
        or(:OLD.GHJSSJ<>:NEW.GHJSSJ)or(:OLD.GHXHS0<>:NEW.GHXHS0)or(:OLD.YYXHS0<>:NEW.YYXHS0)
          or(:OLD.GHHZCK<>:NEW.GHHZCK)  Or(:OLD.YYXHMX<>:NEW.YYXHMX) or (:old.TZBZ00<>:NEW.TZBZ00)
          or (:OLD.SFZJGH<>:NEW.SFZJGH) or (:OLD.PBLX00<>:NEW.PBLX00) Then
       select userenv('SESSIONID') PROCESS into VS_PROCESS from dual where rownum=1;
       Verrmsg:='select YGBH00 from XT_XTRZ00 where JCID00='||VS_PROCESS;
       begin
         select YGBH00 into VS_YGBH00 from XT_XTRZ00 where JCID00=VS_PROCESS and
                LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS);
       exception
         when others then
           VS_YGBH00:=0;
       end;
       select SQ_XT_XTCZRZ_ID0000.NEXTVAL into VD_CZID00 from dual where ROWNUM=1;
       Verrmsg:='select BMMC00 from BM_BMBM00 where BMBH00='||to_char(:OLD.GHKSBH);
       select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00=:OLD.GHKSBH and ROWNUM=1;
       VS_CZNR00 := VS_CZNR00||'科室:'||trim(VS_KSMC00);
       if :OLD.GHKSBH<>:NEW.GHKSBH then
         Verrmsg:='select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00='||to_char(:NEW.GHKSBH);
         select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00=:NEW.GHKSBH and ROWNUM=1;
         VS_CZNR00 := VS_CZNR00||'->'||trim(VS_KSMC00);
       end if;
       VS_CZNR00 := VS_CZNR00||';';

       Verrmsg:='select ZWXM00 from BM_YGBM00 where YGBH00='||to_char(:OLD.GHYSBH);
       select ZWXM00 into VS_YSMC00 from BM_YGBM00 Where YGBH00=:OLD.GHYSBH and ROWNUM=1;
       VS_CZNR00 := VS_CZNR00||'医生:'||trim(VS_YSMC00);
       if :OLD.GHYSBH<>:NEW.GHYSBH then
          Verrmsg:='Select ZWXM00 From BM_YGBM00 Where YGBH00='||to_char(:new.GHYSBH);
          begin
            select ZWXM00 into VS_YSMC00 from BM_YGBM00 where YGBH00=:NEW.GHYSBH and ROWNUM=1;
          exception
            when others then
              VS_YSMC00:='未知';
          end;

          VS_CZNR00 := VS_CZNR00||'->'||trim(VS_YSMC00);
       end if;
       VS_CZNR00 := VS_CZNR00||';';

       if :OLD.GHLBMC<>:NEW.GHLBMC then
          VS_CZNR00 := VS_CZNR00||'挂号类别:'||trim(:OLD.GHLBMC)||'->'||trim(:NEW.GHLBMC)||';';
       --Else
       --   VS_CZNR00 := VS_CZNR00||'挂号类别:'||Trim(:OLD.GHLBMC)||';';
       end if;

       if :OLD.GHHZCK <>:NEW.GHHZCK then
          if :OLD.GHHZCK is not null then
             Verrmsg:='select CKMC00 from BM_CKBM00 where CKBH00='||to_char(:OLD.GHHZCK);
             begin
               select CKMC00 into VS_CKMC00 from BM_CKBM00 where CKBH00=:OLD.GHHZCK and ROWNUM=1;
             exception
               when others then
                 VS_CKMC00:='未知';
             end;
          end if;
          VS_CZNR00 := VS_CZNR00||'地点:'||trim((VS_CKMC00));
          VS_CKMC00 := '';
          if :NEW.GHHZCK is not null then
             Verrmsg:='select CKMC00 from BM_CKBM00 where CKBH00='||to_char(:NEW.GHHZCK);
             begin
               select CKMC00 into VS_CKMC00 from BM_CKBM00 Where CKBH00=:NEW.GHHZCK and ROWNUM=1;
             exception
               when others then
                 VS_CKMC00:='未知';
             end;
          end if;
          VS_CZNR00 := VS_CZNR00||'->'||trim((VS_CKMC00))||';';
       end if;

       if :OLD.GHZKBH<>:NEW.GHZKBH then
          if :OLD.GHZKBH is not null then
            Verrmsg:='select ZKMC00 from VS_ZKMC00 where ZKBM00='||to_char(:OLD.GHZKBH);
            begin
              select ZKMC00 into VS_ZKMC00 from BM_ZKBM00 Where ZKBM00=:OLD.GHZKBH and ROWNUM=1;
            exception
              when others then
                VS_ZKMC00:='未知';
            end;
          end if;
          VS_CZNR00 := VS_CZNR00||'专科:'||trim((VS_ZKMC00));
          VS_ZKMC00 :='';
          if :NEW.GHZKBH is not null then
             Verrmsg:='Select ZKMC00 From VS_ZKMC00 Where ZKBM00='||to_char(:NEW.GHZKBH);
             begin
               select ZKMC00 into VS_ZKMC00 from BM_ZKBM00 where ZKBM00=:NEW.GHZKBH and ROWNUM=1;
             exception
             when others then
               VS_ZKMC00:='未知';
             end;
          end if;
          VS_CZNR00 := VS_CZNR00||'->'||trim((VS_ZKMC00))||';';
       end if;
       if :OLD.YYXHMX<>:NEW.YYXHMX then
          --VS_CZNR00 := VS_CZNR00||'预约限号明细:'||TO_CHAR(:OLD.YYXHMX)||'->'||TO_CHAR(:NEW.YYXHMX)||';';
          VS_CZNR00 := VS_CZNR00||'预约限号明细:'||trim(:OLD.YYXHMX)||'->'||trim(:NEW.YYXHMX)||';';
       end if;

       if :OLD.GHMZSJ<>:NEW.GHMZSJ then
          VS_CZNR00 := VS_CZNR00||'门诊时间:'||trim(:OLD.GHMZSJ)||'->'||trim(:NEW.GHMZSJ)||';';
       else
          VS_CZNR00 := VS_CZNR00||'门诊时间:'||trim(:OLD.GHMZSJ)||';';
       end if;
       if :OLD.GHQSSJ<>:NEW.GHQSSJ then
          VS_CZNR00 := VS_CZNR00||'起始时间:'||trim(:OLD.GHQSSJ)||'->'||trim(:NEW.GHQSSJ)||';';
       else
          VS_CZNR00 := VS_CZNR00||'起始时间:'||trim(:OLD.GHQSSJ)||';';
       end if;
       if :OLD.GHJSSJ<>:NEW.GHJSSJ Then
          VS_CZNR00 := VS_CZNR00||'截止时间:'||trim(:OLD.GHJSSJ)||'->'||trim(:NEW.GHJSSJ)||';';
       else
          VS_CZNR00 := VS_CZNR00||'截止时间:'||trim(:OLD.GHJSSJ)||';';
       end if;

       if :OLD.GHXHS0<>:NEW.GHXHS0 then
          VS_CZNR00 := VS_CZNR00||'挂号限号:'||to_char(:OLD.GHXHS0)||'->'||to_char(:NEW.GHXHS0)||';';
       --Else
       --   VS_CZNR00 := VS_CZNR00||'挂号限号:'||TO_CHAR(:OLD.GHXHS0)||';';
       end if;
       if :OLD.YYXHS0<>:NEW.YYXHS0 then
          VS_CZNR00 := VS_CZNR00||'预约限号:'||to_char(:OLD.YYXHS0)||'->'||to_char(:NEW.YYXHS0)||';';
       --Else
       --   VS_CZNR00 := VS_CZNR00||'预约限号:'||TO_CHAR(:OLD.YYXHS0)||';';
       end if;
       --If :OLD.GHYGHS<>:NEW.GHYGHS Then
       --   VS_CZNR00 := VS_CZNR00||'已挂号:'||TO_CHAR(:OLD.GHYGHS)||'->'||TO_CHAR(:NEW.GHYGHS)||';';
       --Else
      --    VS_CZNR00 := VS_CZNR00||'已挂号:'||TO_CHAR(:OLD.GHYGHS)||';';
       --End If;
       --If :OLD.GHYYHS<>:NEW.GHYYHS Then
       --   VS_CZNR00 := VS_CZNR00||'已预约号:'||TO_CHAR(:OLD.GHYYHS)||'->'||TO_CHAR(:NEW.GHYYHS)||';';
       --Else
       --   VS_CZNR00 := VS_CZNR00||'已预约号:'||TO_CHAR(:OLD.GHYYHS)||';';
       --End If;
       if :OLD.TZBZ00<>:NEW.TZBZ00 then
          VS_CZNR00 := VS_CZNR00||'停诊标志：'||trim(:OLD.TZBZ00)||'->'||trim(:NEW.TZBZ00)||';';
       end if;

       if :OLD.SFZJGH<>:NEW.SFZJGH then
           VS_CZNR00 := VS_CZNR00||'加号:'||trim(:OLD.SFZJGH)||'->'||trim(:NEW.SFZJGH)||';';
       end if;
       
       if :OLD.PBLX00<>:NEW.PBLX00 then
          VS_CZNR00 := VS_CZNR00||'排班类型'||trim(:OLD.PBLX00)||'->'||trim(:NEW.PBLX00)||';';
       end if;
       insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
            values(VD_CZID00,'SF_YSGHHB',1,VS_YGBH00,VS_CZNR00,:OLD.GHHBID);
     end if;
  end if;
exception
  when others then
    RAISE_APPLICATION_ERROR(-20004,'未找到数据！*'||Verrmsg||';'||SQLERRM);
end TR_SF_YSGHHB_ALL;
/