CREATE OR REPLACE TRIGGER
TR_YJ_YWMX00_UPD_PACS
 AFTER 
 INSERT OR DELETE OR UPDATE
 ON SD_YJ.YJ_YWMX00
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
declare ErrStr   varchar2(200);
        ECUSTOM  EXCEPTION;
        VJCLBID  bm_zlzd00.JCLBID%type; --20060427
        VJCLBMC  varchar2(8);
        VMZZYBZ  yj_yw0000.MZZYBZ%type; 
Begin

  IF INSERTING THEN
  
     select MZZYBZ into VMZZYBZ from yj_yw0000 where yjdjh0=:new.yjdjh0;
     if VMZZYBZ<> '1' then
       return;
     end if;
  
    IF :NEW.ZLXMID>0 THEN
      ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid);
      SELECT JCLBID INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID;
    END IF;
    --检查类别处理
    if VJCLBID is not null then
     --插入明细表  未考虑估价情况！！
     --增加apply_id字段,2017-9-11  by-zmx
     select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID;
     insert into spacs.EXAM_APPLY_ITEM(APPLY_NO,APPLY_ID,ITEM_NO,ITEM_CODE,ITEM_NAME,COST,CHARGE,CHARGE_FLAG,exam_class)
          --values(:new.yjdjh0,:new.ID0000,:new.ZLXMID,:new.ZLXMJC,:new.ZJE000,'0');
          select :new.yjdjh0,:new.yjdjh0,:new.ID0000,:new.ZLXMID,:new.XMMC00,
         -- sum(round(a.sfje00*decode(:new.sl0000,null,1,:new.sl0000),2)),
         -- sum(round(a.sfje00*decode(:new.sl0000,null,1,:new.sl0000),2)),
          -- --2011.1.17 edit by lingh, 增加b.sfcs00
          sum(round(a.sfje00*b.sfcs00*decode(:new.sl0000,null,1,:new.sl0000),2)),
          sum(round(a.sfje00*b.sfcs00*decode(:new.sl0000,null,1,:new.sl0000),2)),
          '0',VJCLBMC
            From bm_yysfxm a,xt_zlsfgx b where a.sfxmid=b.sfxmid and b.zlxmid=:new.zlxmid;
     end if;

  ELSIF UPDATING THEN  --更改
  
     select MZZYBZ into VMZZYBZ from yj_yw0000 where yjdjh0=:new.yjdjh0;
     if VMZZYBZ<> '1' then
      return;
    end if;
  
    IF :NEW.ZLXMID>0 THEN
      ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid);
      SELECT JCLBID INTO VJCLBID
      FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID;
    END IF;
    --检查项目变动时
    if  (VJCLBID is not null) and (:NEW.ZLXMID<>:OLD.ZLXMID) then
       delete from spacs.EXAM_APPLY_ITEM where ITEM_NO=:old.ID0000;
       --插入明细表
       insert into spacs.EXAM_APPLY_ITEM(APPLY_NO,APPLY_ID,ITEM_NO,ITEM_CODE,ITEM_NAME,COST,CHARGE,CHARGE_FLAG,exam_class)
              select :new.yjdjh0,:new.yjdjh0,:new.ID0000,:new.ZLXMID,:new.XMMC00,
              --round(a.sfje00*decode(:new.sl0000,null,1,:new.sl0000),2),
              --round(a.sfje00*decode(:new.sl0000,null,1,:new.sl0000),2),
              -- --2011.1.17 edit by lingh, 增加b.sfcs00
              round(a.sfje00*b.sfcs00*decode(:new.sl0000,null,1,:new.sl0000),2),
              round(a.sfje00*b.sfcs00*decode(:new.sl0000,null,1,:new.sl0000),2),
              '0',VJCLBMC
            From bm_yysfxm a,xt_zlsfgx b where a.sfxmid=b.sfxmid and b.zlxmid=:new.zlxmid;
     end if;
    --检查类别项目重新计价
    if :new.sl0000<>:old.sl0000 and VJCLBID is not null then
      update spacs.EXAM_APPLY_ITEM set cost=(select
      round(a.sfje00*b.sfcs00*:new.sl0000,2)
            From bm_yysfxm a,xt_zlsfgx b where a.sfxmid=b.sfxmid and b.zlxmid=:old.zlxmid) where ITEM_NO=:old.ID0000;
    end if;

 /* ELSIF DELETING THEN
  
     select MZZYBZ into VMZZYBZ from yj_yw0000 where yjdjh0=:old.yjdjh0;
     if VMZZYBZ<> '1' then
       return;
    end if;
  
    IF :old.ZLXMID>0 THEN
      ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:old.zlxmid);
      SELECT JCLBID INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:old.ZLXMID;
    END IF;
    IF VJCLBID is not null then
       delete from spacs.EXAM_APPLY_ITEM where ITEM_NO=:old.ID0000;
    END IF;*/
  END IF;
  EXCEPTION
    WHEN ECUSTOM THEN
      RAISE_APPLICATION_ERROR(-20030,ERRSTR||'！*');
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20010,'数据没有找到,'||errStr||'！*');
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*'));
End;