CREATE OR REPLACE TRIGGER TR_BM_ZLZD00_ALL_CZRZ
 BEFORE 
 INSERT OR DELETE OR UPDATE
 ON BM_ZLZD00
 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_BMMC00 BM_BMBM00.BMMC00%Type;
  Verrmsg   varchar2(100);
  IS_ERROR    varchar2(100);
Begin
    Verrmsg:='?';
    --  If DELETING OR UPDATING OR INSERTING
    if inserting then  --插入
       Verrmsg:='inserting';
       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.ZXKS00 Is Not Null Then
          Select BMMC00 Into VS_BMMC00 From BM_BMBM00 Where BMBH00=:NEW.ZXKS00 And ROWNUM=1;
       End If;
       VS_CZNR00 := VS_CZNR00||'简称:'||Trim((:NEW.ZLXMJC))||';';
       VS_CZNR00 := VS_CZNR00||'固定:'||Trim((:NEW.GDXMBZ))||';';
       VS_CZNR00 := VS_CZNR00||'分步:'||Trim((:NEW.FBZXBZ))||';';
       VS_CZNR00 := VS_CZNR00||'参考值:'||Trim((:NEW.CKZ000))||';';
       VS_CZNR00 := VS_CZNR00||'结果单位:'||Trim((:NEW.JGDW00))||';';
       VS_CZNR00 := VS_CZNR00||'单位:'||Trim((:NEW.DW0000))||';';
       VS_CZNR00 := VS_CZNR00||'备注:'||Trim((:NEW.BZ0000))||';';
       VS_CZNR00 := VS_CZNR00||'启用日期:'||Trim((:NEW.QYRQ00))||';';
       VS_CZNR00 := VS_CZNR00||'允许申请:'||Trim((:NEW.SFYXSQ))||';';
       VS_CZNR00 := VS_CZNR00||'执行科室:'||Trim((VS_BMMC00))||';';
       VS_CZNR00 := VS_CZNR00||'组合标志:'||Trim((:NEW.ZHXMBZ))||';';
       Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
              Values(VD_CZID00,'BM_ZLZD00',0,VS_YGBH00,'(新增)'||VS_CZNR00,:NEW.ZLXMID);
       --end insert       
    elsif deleting then --删除
       Verrmsg:='inserting';  
       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.ZXKS00 Is Not Null Then
          Select BMMC00 Into VS_BMMC00 From BM_BMBM00 Where BMBH00=:old.ZXKS00 And ROWNUM=1;
       End If;
       VS_CZNR00 := VS_CZNR00||'内码:'||to_char(:old.ZLXMID)||';';
       VS_CZNR00 := VS_CZNR00||'简称:'||Trim((:old.ZLXMJC))||';';
       VS_CZNR00 := VS_CZNR00||'固定:'||Trim((:old.GDXMBZ))||';';
       VS_CZNR00 := VS_CZNR00||'分步:'||Trim((:old.FBZXBZ))||';';
       VS_CZNR00 := VS_CZNR00||'参考值:'||Trim((:old.CKZ000))||';';
       VS_CZNR00 := VS_CZNR00||'结果单位:'||Trim((:old.JGDW00))||';';
       VS_CZNR00 := VS_CZNR00||'单位:'||Trim((:old.DW0000))||';';
       VS_CZNR00 := VS_CZNR00||'备注:'||Trim((:old.BZ0000))||';';
       VS_CZNR00 := VS_CZNR00||'启用日期:'||Trim((:old.QYRQ00))||';';
       VS_CZNR00 := VS_CZNR00||'允许申请:'||Trim((:old.SFYXSQ))||';';
       VS_CZNR00 := VS_CZNR00||'执行科室:'||Trim((VS_BMMC00))||';';
       VS_CZNR00 := VS_CZNR00||'组合标志:'||Trim((:old.ZHXMBZ))||';';
       Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
              Values(VD_CZID00,'BM_ZLZD00',2,VS_YGBH00,'(删除)'||VS_CZNR00,:old.ZLXMID); 
       --end delete       
    elsif UPDATING then  --修改
       Verrmsg:='UPDATING';
       IS_ERROR :='@1@';
       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;
          IS_ERROR :='@2@';
       end;
       SELECT SQ_XT_XTCZRZ_ID0000.NEXTVAL INTO VD_CZID00 FROM DUAL WHERE ROWNUM=1;
      
       If nvl(:OLD.ZLXMJC,' ')<>nvl(:NEW.ZLXMJC,' ') Then
           VS_CZNR00 := VS_CZNR00||'简称:'||Trim(:OLD.ZLXMJC)||'->'||Trim(:NEW.ZLXMJC)||';';
       end if;
       If nvl(:OLD.GDXMBZ,' ')<>nvl(:NEW.GDXMBZ,' ') Then
           VS_CZNR00 := VS_CZNR00||'固定:'||Trim(:OLD.GDXMBZ)||'->'||Trim(:NEW.GDXMBZ)||';';
       end if;
       If nvl(:OLD.FBZXBZ,' ')<>nvl(:NEW.FBZXBZ,' ') Then
           VS_CZNR00 := VS_CZNR00||'分步:'||Trim(:OLD.FBZXBZ)||'->'||Trim(:NEW.FBZXBZ)||';';
       end if;
       If nvl(:OLD.CKZ000,' ')<>nvl(:NEW.CKZ000,' ') Then
           VS_CZNR00 := VS_CZNR00||'参考值:'||Trim(:OLD.CKZ000)||'->'||Trim(:NEW.CKZ000)||';';
       end if;
       If nvl(:OLD.JGDW00,' ')<>nvl(:NEW.JGDW00,' ') Then
           VS_CZNR00 := VS_CZNR00||'结果单位:'||Trim(:OLD.JGDW00)||'->'||Trim(:NEW.JGDW00)||';';
       end if;
       If nvl(:OLD.DW0000,' ')<>nvl(:NEW.DW0000,' ') Then
           VS_CZNR00 := VS_CZNR00||'单位:'||Trim(:OLD.DW0000)||'->'||Trim(:NEW.DW0000)||';';
       end if;
       If nvl(:OLD.XXMS00,' ')<>nvl(:NEW.XXMS00,' ') Then
           VS_CZNR00 := VS_CZNR00||'详细描述:'||Trim(:OLD.XXMS00)||'->'||Trim(:NEW.XXMS00)||';';
       end if;
       If nvl(:OLD.BZ0000,' ')<>nvl(:NEW.BZ0000,' ') Then
           VS_CZNR00 := VS_CZNR00||'备注:'||Trim(:OLD.BZ0000)||'->'||Trim(:NEW.BZ0000)||';';
       end if;
       If nvl(:OLD.QYRQ00,' ')<>nvl(:NEW.QYRQ00,' ') Then
           VS_CZNR00 := VS_CZNR00||'启用日期:'||Trim(:OLD.QYRQ00)||'->'||Trim(:NEW.QYRQ00)||';';
       end if;
       If nvl(:OLD.SFYXSQ,' ')<>nvl(:NEW.SFYXSQ,' ') Then
           VS_CZNR00 := VS_CZNR00||'申请标志:'||Trim(:OLD.SFYXSQ)||'->'||Trim(:NEW.SFYXSQ)||';';
       end if;
       If nvl(:OLD.ZHXMBZ,' ')<>nvl(:NEW.ZHXMBZ,' ') Then
           VS_CZNR00 := VS_CZNR00||'组合标志:'||Trim(:OLD.ZHXMBZ)||'->'||Trim(:NEW.ZHXMBZ)||';';
       end if;
       If nvl(:OLD.ZLXMBH,' ')<>nvl(:NEW.ZLXMBH,' ') Then
           VS_CZNR00 := VS_CZNR00||'项目编号:'||Trim(:OLD.ZLXMBH)||'->'||Trim(:NEW.ZLXMBH)||';';
       end if;
       If nvl(:OLD.LBBZ00,' ')<>nvl(:NEW.LBBZ00,' ') Then
           VS_CZNR00 := VS_CZNR00||'项目编号:'||Trim(:OLD.LBBZ00)||'->'||Trim(:NEW.LBBZ00)||';';
       end if;
       If nvl(:OLD.WBSM00,' ')<>nvl(:NEW.WBSM00,' ') Then
           VS_CZNR00 := VS_CZNR00||'五笔首码:'||Trim(:OLD.WBSM00)||'->'||Trim(:NEW.WBSM00)||';';
       end if;
       If nvl(:OLD.PYSM00,' ')<>nvl(:NEW.PYSM00,' ') Then
           VS_CZNR00 := VS_CZNR00||'拼音首码:'||Trim(:OLD.PYSM00)||'->'||Trim(:NEW.PYSM00)||';';
       end if;
       If nvl(:OLD.PYSM00,' ')<>nvl(:NEW.PYSM00,' ') Then
           VS_CZNR00 := VS_CZNR00||'拼音首码:'||Trim(:OLD.PYSM00)||'->'||Trim(:NEW.PYSM00)||';';
       end if;
       If nvl(:OLD.TZRQ00,' ')<>nvl(:NEW.TZRQ00,' ') Then
           VS_CZNR00 := VS_CZNR00||'停止日期:'||Trim(:OLD.TZRQ00)||'->'||Trim(:NEW.TZRQ00)||';';
       end if;
       If nvl(:OLD.DLBQZX,' ')<>nvl(:NEW.DLBQZX,' ') Then
           VS_CZNR00 := VS_CZNR00||'住院执行:'||Trim(:OLD.DLBQZX)||'->'||Trim(:NEW.DLBQZX)||';';
       end if;
       If nvl(:OLD.DLKSZX,' ')<>nvl(:NEW.DLKSZX,' ') Then
           VS_CZNR00 := VS_CZNR00||'门诊执行:'||Trim(:OLD.DLKSZX)||'->'||Trim(:NEW.DLKSZX)||';';
       end if;
       If nvl(:OLD.BQSFBZ,' ')<>nvl(:NEW.BQSFBZ,' ') Then
           VS_CZNR00 := VS_CZNR00||'病区收费:'||Trim(:OLD.BQSFBZ)||'->'||Trim(:NEW.BQSFBZ)||';';
       end if;
       If nvl(:OLD.BQDYBZ,' ')<>nvl(:NEW.BQDYBZ,' ') Then
           VS_CZNR00 := VS_CZNR00||'病区打印:'||Trim(:OLD.BQDYBZ)||'->'||Trim(:NEW.BQDYBZ)||';';
       end if;
       If nvl(:OLD.BBID00,' ')<>nvl(:NEW.BBID00,' ') Then
           VS_CZNR00 := VS_CZNR00||'标本:'||Trim(:OLD.BBID00)||'->'||Trim(:NEW.BBID00)||';';
       end if;
       If nvl(:OLD.CM3000,' ')<>nvl(:NEW.CM3000,' ') Then
           VS_CZNR00 := VS_CZNR00||'CM3:'||Trim(:OLD.CM3000)||'->'||Trim(:NEW.CM3000)||';';
       end if;
       If nvl(:OLD.JKBH01,' ')<>nvl(:NEW.JKBH01,' ') Then
           VS_CZNR00 := VS_CZNR00||'接口编号:'||Trim(:OLD.JKBH01)||'->'||Trim(:NEW.JKBH01)||';';
       end if;
       If nvl(:OLD.JYTZBH,' ')<>nvl(:NEW.JYTZBH,' ') Then
           VS_CZNR00 := VS_CZNR00||'检验组号:'||Trim(:OLD.JYTZBH)||'->'||Trim(:NEW.JYTZBH)||';';
       end if;
       If nvl(:OLD.TMSFWK,' ')<>nvl(:NEW.TMSFWK,' ') Then
           VS_CZNR00 := VS_CZNR00||'条码为空:'||Trim(:OLD.TMSFWK)||'->'||Trim(:NEW.TMSFWK)||';';
       end if;
       If nvl(:OLD.TJBZ00,' ')<>nvl(:NEW.TJBZ00,' ') Then
           VS_CZNR00 := VS_CZNR00||'体检项目:'||Trim(:OLD.TJBZ00)||'->'||Trim(:NEW.TJBZ00)||';';
       end if;
       If nvl(:OLD.JCQZYSX,' ')<>nvl(:NEW.JCQZYSX,' ') Then
           VS_CZNR00 := VS_CZNR00||'注意事项:'||Trim(:OLD.JCQZYSX)||'->'||Trim(:NEW.JCQZYSX)||';';
       end if;
       If nvl(:OLD.BH0000,' ')<>nvl(:NEW.BH0000,' ') Then
           VS_CZNR00 := VS_CZNR00||'编号:'||Trim(:OLD.BH0000)||'->'||Trim(:NEW.BH0000)||';';
       end if;
       --If nvl(:OLD.BBID01,' ')<>nvl(:NEW.BBID01,' ') Then
       --    VS_CZNR00 := VS_CZNR00||'标本ID:'||Trim(:OLD.BBID01)||'->'||Trim(:NEW.BBID01)||';';
       --end if;
       If nvl(:OLD.SFXYTZ,' ')<>nvl(:NEW.SFXYTZ,' ') Then
           VS_CZNR00 := VS_CZNR00||'体征标志:'||Trim(:OLD.SFXYTZ)||'->'||Trim(:NEW.SFXYTZ)||';';
       end if;
       IS_ERROR:='@3@';
       if nvl(:old.ZXKS00,0)<>nvl(:new.ZXKS00,0) Then
        IS_ERROR:='@4@';
              Select BMMC00 Into VS_BMMC00 From BM_BMBM00 Where BMBH00=:old.ZXKS00 And ROWNUM=1;
                 IS_ERROR:='@5@';
              VS_CZNR00 := VS_CZNR00||'执行科室:'||nvl(VS_BMMC00,'无');
             IF(:new.ZXKS00 IS NULL) THEN
              VS_CZNR00 := VS_CZNR00||'->'||'无'||';';
              ELSE
              Select BMMC00 Into VS_BMMC00 From BM_BMBM00 Where BMBH00=NVL(:new.ZXKS00,'0') And ROWNUM=1;
              VS_CZNR00 := VS_CZNR00||'->'||nvl(VS_BMMC00,'无')||';';
              END IF ;
       end If;
       if nvl(:old.ZYZBID,0)<>nvl(:new.ZYZBID,0) Then
           VS_CZNR00 := VS_CZNR00||'检验组:'||to_char(:OLD.ZYZBID)||'->'||to_char(:NEW.ZYZBID)||';';
       end If;
       if nvl(:old.SSDJBH,0)<>nvl(:new.SSDJBH,0) Then
           VS_CZNR00 := VS_CZNR00||'手术等级:'||to_char(:OLD.SSDJBH)||'->'||to_char(:NEW.SSDJBH)||';';
       end If;
       if nvl(:old.LBBH00,0)<>nvl(:new.LBBH00,0) Then
           VS_CZNR00 := VS_CZNR00||'类别:'||to_char(:OLD.LBBH00)||'->'||to_char(:NEW.LBBH00)||';';
       end If;
       --if :old.ZSLBID<>:new.ZSLBID Then
       --    VS_CZNR00 := VS_CZNR00||'ZSLBID:'||to_char(:OLD.ZSLBID)||'->'||to_char(:NEW.ZSLBID)||';';
       --end If;
       --if :old.BSLBID<>:new.BSLBID Then
       --    VS_CZNR00 := VS_CZNR00||'检验组:'||to_char(:OLD.BSLBID)||'->'||to_char(:NEW.BSLBID)||';';
       --end If;
       
       if nvl(:old.SQDID0,0)<>nvl(:new.SQDID0,0) Then
           VS_CZNR00 := VS_CZNR00||'电子申请单:'||to_char(:OLD.SQDID0)||'->'||to_char(:NEW.SQDID0)||';';
       end If;

       if nvl(:old.JCLBID,0)<>nvl(:new.JCLBID,0) Then
           VS_CZNR00 := VS_CZNR00||'检查类别:'||to_char(:OLD.JCLBID)||'->'||to_char(:NEW.JCLBID)||';';
       end If;
       if nvl(:old.TMDYCS,0)<>nvl(:new.TMDYCS,0) Then
           VS_CZNR00 := VS_CZNR00||'条码打印次数:'||to_char(:OLD.TMDYCS)||'->'||to_char(:NEW.TMDYCS)||';';
       end If;
       
       Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
             Values(VD_CZID00,'BM_ZLZD00',1,VS_YGBH00,'(修改:'||:NEW.ZLXMJC||')'||VS_CZNR00,:old.ZLXMID); 
  end if;
  --end update
exception

  when others then
    RAISE_APPLICATION_ERROR(-20004,'未找到数据!*'||IS_ERROR||Verrmsg||';'||SQLERRM);

end ;
/