CREATE OR REPLACE TRIGGER TR_BM_YBSFDY_CZRZ
 BEFORE 
 DELETE OR UPDATE
 ON BM_YBSFDY
 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;
  VS_CZNR00 XT_XTCZRZ.CZNR00%Type;
  VS_YPBM00 BM_YD0000.YPBM00%Type;
  VS_YPMC00 BM_YD0000.YPMC00%Type;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- LIUJ     2010.11.18      CREATE
-- CSF      2010.12.15      BM_YBSFDY 中的SFXMID不一定都在BM_YD0000中，所以Select ypbm00,ypmc00 into VS_YPBM00,VS_YPMC00 from bm_yd0000 where ypnm00=:OLD.SFXMID 语句加异常保护
Begin
  Select userenv('SESSIONID') PROCESS Into VS_PROCESS from dual where rownum=1;
  Select nvl(max(YGBH00),0) Into VS_YGBH00 From XT_XTRZ00 Where JCID00=VS_PROCESS And
         LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS);
/*  begin
    Select ypbm00,ypmc00 into VS_YPBM00,VS_YPMC00 from bm_yd0000 where ypnm00=:OLD.SFXMID;
  exception
    when others then
      VS_YPBM00:=' ';
      VS_YPMC00:='非药品';
  end;*/
  If Trim(:NEW.SFYP00)='Y' Then
         Begin
           Select YPMC00||'('||YPBM00||')' Into VS_CZNR00 From BM_YD0000 Where YPNM00=:Old.SFXMID And Rownum=1;
           VS_CZNR00 := '药品:'||VS_CZNR00||';';
         Exception
           when others Then
             VS_CZNR00:='药品:';
         End;
       Else
         Begin
           Select XMMC00||'('||BH0000||')' Into VS_CZNR00 From BM_YYSFXM Where SFXMID=:Old.SFXMID And Rownum=1;
           VS_CZNR00 := '医疗项目:'||VS_CZNR00||';';
         Exception
           when others Then
             VS_CZNR00:='医疗项目:';
         End;
       End If;
       
  if UPDATING then
    --VS_CZNR00:='修改:('||VS_YPBM00||'　'||VS_YPMC00||')';
       If nvl(trim(:NEW.YBXMBH),' ') <> nvl(trim(:OLD.YBXMBH),' ') Then
         VS_CZNR00 := VS_CZNR00||'医保编码:'||TRIM(:OLD.YBXMBH)||'->'||TRIM(:NEW.YBXMBH)||';   ';
       End If;       
       If nvl(trim(:NEW.YBZYFP),' ') <> nvl(trim(:OLD.YBZYFP),' ') Then
         VS_CZNR00 := VS_CZNR00||'住院发票类别:'||TRIM(:OLD.YBZYFP)||'->'||TRIM(:NEW.YBZYFP)||';   ';
       End If;
       If nvl(trim(:NEW.YBMZFP),' ') <> nvl(trim(:OLD.YBMZFP),' ') Then
         VS_CZNR00 := VS_CZNR00||'门诊发票类别:'||TRIM(:OLD.YBMZFP)||'->'||TRIM(:NEW.YBMZFP)||';   ';
       End If;
       If NVL(trim(:OLD.SFYBXM),' ') <> NVL(trim(:NEW.SFYBXM),' ') Then
           VS_CZNR00 :=VS_CZNR00||'是否医保项目:'||Trim(:OLD.SFYBXM)||'->'||Trim(:NEW.SFYBXM)||';';
       End If;
       If NVL(trim(:OLD.YBLB00),' ')<>NVL(trim(:NEW.YBLB00),' ') Then
           VS_CZNR00 :=VS_CZNR00||'医保类别:'||Trim(:OLD.YBLB00)||'->'||Trim(:NEW.YBLB00)||';';
       End If;
       If NVL(:OLD.ZFBL00,0)<>NVL(:NEW.ZFBL00,0) Then
           VS_CZNR00 :=VS_CZNR00||'自付比例:'||to_char(:OLD.ZFBL00)||'->'||to_char(:NEW.ZFBL00)||';';
       End If;
       If NVL(trim(:OLD.BZ0000),' ') <> NVL(trim(:NEW.BZ0000),' ') Then
           VS_CZNR00 :=VS_CZNR00||'备注:'||Trim(:OLD.BZ0000)||'->'||Trim(:NEW.BZ0000)||';';
       End If;
       If NVL(trim(:OLD.YBBRLB),' ')<>NVL(trim(:NEW.YBBRLB),' ') Then
           VS_CZNR00 :=VS_CZNR00||'医保病人类别:'||Trim(:OLD.YBBRLB)||'->'||Trim(:NEW.YBBRLB)||';';
       End If;
      If NVL(trim(:OLD.BKEA96),' ')<>NVL(trim(:NEW.BKEA96),' ') Then
           VS_CZNR00 :=VS_CZNR00||'BKEA96:'||Trim(:OLD.BKEA96)||'->'||Trim(:NEW.BKEA96)||';';
       End If;
   If NVL(trim(:OLD.MZZYBZ),' ')<>NVL(trim(:NEW.MZZYBZ),' ') Then
           VS_CZNR00 :=VS_CZNR00||'MZZYBZ:'||Trim(:OLD.MZZYBZ)||'->'||Trim(:NEW.MZZYBZ)||';';
       End If;
       Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) 
           Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'BM_YBSFDY',1,VS_YGBH00,VS_CZNR00,:OLD.SFXMID);
             
  Elsif DELETING Then
     VS_CZNR00 := '删除:('||VS_YPBM00||'　'||VS_YPMC00||')';
              
     Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) 
       Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'BM_YBSFDY',1,VS_YGBH00,VS_CZNR00,:OLD.SFXMID);
  END IF;                    
End;
/