CREATE OR REPLACE TRIGGER tr_sf_bm_brxxb0_insert BEFORE INSERT OR UPDATE OF sfyd00, brcsrq, yblb00, ybbrlb, tydwmc, fbbh00, brzjbh ON bm_brxxb0 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW declare ls_BRID00 BM_BRXXB0.BRID00%type; Vlnzdyd char(1); --老年自动优待 Vlnzdyd1 char(1); --老年自动优待 ls_SFLNYD char(1); --是否老年优待 Vzdydfb varchar2(50); --自动优待费别 Vcounter number(5); Ecustom exception; --错误变量 Verrmsg varchar2(255); --错误提示变量 ls_JKZDQF varchar2(20); ls_LXBR00 char(1); ls_YBZXLB IC_YBBRLB.YBZXLB%type; ls_SFXNH0 IC_YBBRLB.SFXNH0%type; begin if inserting then ls_BRID00 := 0; if substr(:NEW.BRBLH0, 1, 2) <> 'PE' then select nvl(max(BRID00),0) into ls_BRID00 from BM_BRXXB0 where BRBLH0 = :NEW.BRBLH0 and :NEW.BRBLH0 <> '0'; if ls_BRID00 <> 0 then --该病历号的病人已有记录 if ls_BRID00 <> :NEW.BRID00 then Verrmsg:= '该病历号已被别的病人所使用!'||'!*'; raise Ecustom; end if; end if; end if; select count(1) into Vcounter from XT_XTCS00 where name00='SF_JDWDWID' and value0='Y'; if Vcounter>0 and :NEW.WDWID0 is null then :NEW.WDWID0 := lpad(:NEW.BRID00,10,'0'); end if; :NEW.PYSM00 := SF_XT_GETPYSM(:NEW.BRXM00); :NEW.WBSM00 := SF_XT_GETWBSM(:NEW.BRXM00); if :new.TYDWID is not null then insert into BM_BRTYDW (LSH000,BRID00,TYDWID,CJR000) values (SQ_BM_BRTYDW_LSH000.nextval,:NEW.BRID00,:NEW.TYDWID,:NEW.CZY000); end if; if :new.SFYD00='0'then ls_SFLNYD:='N'; --两费病人自动优待 select nvl(max(trim(value0)),';0,0;') into Vzdydfb from XT_XTCS00 where name00='SF_ZDYDFB'; if Vzdydfb<>';0,0;' then select count(*) into Vcounter from IC_YBBRLB b where :new.FBBH00=b.FBBH00 and :new.YBLB00=b.YBLB00 and instrb(Vzdydfb,';'||to_char(:new.FBBH00)||','||trim(b.YBZXLB)||';')>0; if Vcounter>0 then :new.SFYD00:='1'; ls_SFLNYD:='Y'; end if; end if; if ls_SFLNYD<>'Y' then --要求70岁以上的老人,两费病人自动优待,免除挂号费 select substrb(nvl(max(trim(value0)),'N'),1,1) into Vlnzdyd from XT_XTCS00 where name00='SF_LNZDYD'; if Vlnzdyd='Y' then if (sysdate-to_date(:new.BRCSRQ,'YYYYMMDD'))>70*365 and (substrb(:new.BRZJBH,7,6)=substrb(:new.BRCSRQ,3,6) or substrb(:new.BRZJBH,7,8)=:new.BRCSRQ) then :new.SFYD00:='1'; end if; end if; end if; end if; --变更身份证号 elsif updating and nvl(:old.BRZJBH,' ')<>nvl(:new.BRZJBH,' ') then --要求70岁以上的老人,两费病人自动优待,免除挂号费 select substrb(nvl(max(trim(value0)),'N'),1,1) into Vlnzdyd1 from XT_XTCS00 where name00='SF_LNZDYD1'; if Vlnzdyd1='Y' then if(sysdate-to_date(:old.BRCSRQ,'YYYYMMDD'))>70*365 and (substrb(:old.BRZJBH,7,6)=substrb(:old.BRCSRQ,3,6) or substrb(:old.BRZJBH,7,8)=:old.BRCSRQ) then Verrmsg:='70岁以上优待病人，不允许修改身份证号!*'; raise Ecustom; end if; end if; select substrb(nvl(max(trim(value0)),'N'),1,1) into Vlnzdyd from XT_XTCS00 where name00='SF_LNZDYD'; if Vlnzdyd='Y' then if (sysdate-to_date(:new.BRCSRQ,'YYYYMMDD'))>70*365 and (substrb(:new.BRZJBH,7,6)=substrb(:new.BRCSRQ,3,6) or substrb(:new.BRZJBH,7,8)=:new.BRCSRQ) then :new.SFYD00:='1'; end if; end if; end if; --变更病人身份 if updating and (:old.FBBH00<>:new.FBBH00 or :new.YBLB00<>:old.YBLB00 or :new.YBBRLB<>:old.YBBRLB or :old.SFYD00<>:new.SFYD00) then select count(*) into Vcounter from SF_BRZHXX where BRID00=:new.BRID00; if Vcounter>0 then --建卡时自动允许欠费方式和金额，格式如 3,10，用逗号格式 第一位表示方式 0不允许 1所有病人 2医保病人 3离休病人 4优待病人 5医保(新农合除外) 6新农合(医保除外);逗号后表示金额 select nvl(max(trim(VALUE0)),'0,0') into ls_JKZDQF from XT_XTCS00 where NAME00='SF_JKZDQF'; if substrb(ls_JKZDQF,1,1)='1' then update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00; elsif substrb(ls_JKZDQF,1,1)='2' then if :new.FBBH00=3 then update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00; else update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00; end if; elsif substrb(ls_JKZDQF,1,1)='3' then if :new.FBBH00<>3 then update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00; else select b.YBZXLB into ls_YBZXLB FROM IC_YBBRLB b,BM_YBBRLB c where b.FBBH00=:new.FBBH00 and b.YBLB00=:new.YBLB00 and c.YBZXLB=b.YBZXLB and c.YBBRLB=:new.YBBRLB and c.FBBH00=:new.FBBH00; select decode(count(*),0,'0','1') into ls_LXBR00 from BM_LXBRDW where YBZXLB=ls_YBZXLB and DWMC00=:new.TYDWMC; if ls_LXBR00='1' then update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00; else update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00; end if; end if; elsif substrb(ls_JKZDQF,1,1)='4' then if :new.SFYD00='1' and :new.FBBH00=3 then update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00; else update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00; end if; elsif substrb(ls_JKZDQF,1,1)='5' then select nvl(max(SFXNH0),'0') into ls_SFXNH0 from IC_YBBRLB where FBBH00 = :new.FBBH00 and YBLB00 = :new.YBLB00; if :new.FBBH00=3 and ls_SFXNH0<>'1' and ls_SFXNH0<>'4' then update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00; else update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00; end if; elsif substrb(ls_JKZDQF,1,1)='6' then select nvl(max(SFXNH0),'0') into ls_SFXNH0 from IC_YBBRLB where FBBH00 = :new.FBBH00 and YBLB00 = :new.YBLB00; if (:new.FBBH00=3) and (ls_SFXNH0='1' or ls_SFXNH0='4') then update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00; else update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00; end if; end if; end if; end if; if :NEW.BRCSRQ='00000000' then Verrmsg:= '输入的病人出生日期非法!*'||' ("00000000")'; raise Ecustom; end if; if instr(:NEW.TYDWMC,chr(9))>0 or instr(:NEW.TYDWMC,'|')>0 or instr(:NEW.TYDWMC,'^')>0 or instr(:NEW.TYDWMC,'?')>0 or instr(:NEW.TYDWMC,' ')>0 or instr(:NEW.TYDWMC,'弝')>0 or instr(:NEW.TYDWMC,'\')>0 or instr(:NEW.TYDWMC,'')>0 or instr(:NEW.TYDWMC,'{')>0 or instr(:NEW.TYDWMC,'~')>0 then :NEW.TYDWMC := null; end if; exception when Ecustom then raise_application_error(-20911,Verrmsg); rollback; when others then raise_application_error(-20912,substr(nvl(sqlerrm,'出错原因不详'),1,40)||'，请记录此信息并和系统管理员联系！'||'!*'); rollback; end; /