CREATE OR REPLACE TRIGGER TR_YJ_YW0000_UPD_PACS
 AFTER 
 INSERT OR DELETE OR UPDATE
 ON SD_YJ.YJ_YW0000
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
declare ErrStr   varchar2(200);
        ECUSTOM  EXCEPTION;
        VJCLBID  bm_zlzd00.JCLBID%type; --20060427
        VICKH00  ic_yhxx00.ICKH00%type;--定义为与此字段同类型，可变化
        VJCLBMC  varchar2(20);
Begin
  IF INSERTING THEN
    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
     ErrStr:='err2: 检查类别不存在JCLBID='||VJCLBID;
     select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID;
     begin
        select ickh00 into VICKH00 from ic_yhxx00 where brid00=:new.brid00 and ZT0000='1';
        EXCEPTION
          when others  then
          VICKH00:='';
     end;
     --主表

     insert into spacs.EXAM_APPLY@LINKPACS
            (APPLY_NO,SICK_ID,IC_CARD,NAME,SEX,BIRTH_DATE,CHARGE_TYPE,
             MAILING_ADDRESS,ZIP_CODE,PHONE_NUMBER,OUTPATIENT_NO,INPATIENT_NO,
             BED_NO,PATIENT_SOURCE,PRIORITY_ORDER,EXAM_CLASS,REQ_DEPT,REQ_PHYSICIAN,
             REQ_DATE,REQ_TIME,REQ_MEMO,CLIN_DIAG,PERFORM_DEPT,CHARGE_FLAG,visit_id,mi_card,
             exam_sub_class,identity_card,RELEVANT_LAB_TEST)
            select to_char(:new.yjdjh0),brid00 ,trim(VICKH00),BRXM00,BRXB00,to_char(to_date(BRCSRQ,'yyyymmdd'),'yyyy-mm-dd'),substrb(FBMC00,1,8),
             substr(BRJTDZ,1,30),BRYB00,SUBSTR(BRDH00,1,16),decode(:new.MZZYBZ,'0',trim(:NEW.ZYHGHH),''),decode(:new.MZZYBZ,1,trim(:NEW.ZYHGHH),''),
             :new.CH0000,decode(:new.MZZYBZ,0,'门诊',1,'住院','外来'),decode(:new.JZBZ00,'Y','急诊','普通'),VJCLBMC,:new.kdksbh,:new.KDYSXM,
             to_char(to_date(:new.kdrq00,'yyyymmdd'),'yyyy-mm-dd'),:new.kdsj00,:new.ZLXMJC,substr(:NEW.ZDMC00,1,100),:new.YJKSBH,decode(:NEW.SFDJH0,0,'0','1'),:NEW.zyghid,decode(trim(ybkh00),'0','',trim(ybkh00)),
             decode(:NEW.zlxmid,22485,'胃镜',22317,'肠镜',''),CHECK_IDCARD@LINKPACS(BRZJBH),:new.bwmc00 from VW_BM_BRXXB1 where brid00=:new.brid00;
 /*    insert into spacs.EXAM_APPLY_ORGAN@LINKPACS
            (APPLY_NO,ORGAN_NO,ORGAN_NAME)
            SELECT to_char(:new.yjdjh0),'1',TRIM(:new.bwmc00) FROM DUAL; */

     end if;
  ELSIF DELETING THEN
    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@LINKPACS where APPLY_NO=to_char(:old.yjdjh0);
   /*     delete from spacs.EXAM_APPLY_ORGAN@LINKPACS where APPLY_NO=to_char(:old.yjdjh0);*/
    END IF;
  END IF;
  EXCEPTION
    WHEN ECUSTOM THEN
      RAISE_APPLICATION_ERROR(-20030,ERRSTR||'！*');
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20010,'数据没有找到,'||errStr||'！*');
End;
/