--只有5.0的程序才使用这个触发器
--PROMPT   电子病历自动产生ZY_BRXXB0的医生组信息 
CREATE OR REPLACE TRIGGER TR_ZY_BRXXB0_UPDATE_DZBLYSZ
before UPDATE OF YSZID0
ON ZY_BRXXB0
FOR EACH ROW
DECLARE
    COUNTER  NUMBER(3);
    v_YSZID0 ZS_YSZ000.YSZID0%TYPE;  --新医生组ID
    v_YSZMC0 ZS_YSZ000.YSZMC0%TYPE;  --新医生组名称
    v_OYSZID ZS_YSZ000.YSZID0%TYPE;  --旧医生组ID
    v_OYSZMC ZS_YSZ000.YSZMC0%TYPE;  --旧医生组名称
    v_OKSBH0 BM_BMBM00.BMBH00%TYPE;  --原科室编号
    v_OKSMC0 BM_BMBM00.BMMC00%TYPE;  --原科室名称
    v_NKSMC0 BM_BMBM00.BMMC00%TYPE;  --新科室名称
    v_YSBH00 BM_YGBM00.YGBH00%TYPE;  --医生编号
    v_DYSZID ZS_YSZ000.YSZID0%TYPE;  --多个医生的医生组ID    
    v_ZZYS00 BM_YGBM00.YGBH00%TYPE;  --主治医生
    v_ZRYS00 BM_YGBM00.YGBH00%TYPE;  --主任医生
    v_BRZT00 number;
    PRAGMA AUTONOMOUS_TRANSACTION; 
-- MODIFICATION HISTORY
-- Person       Date        Comments
      
Begin
  select count(*) into v_BRZT00 from zy_brxxb0 where zyid00=:new.zyid00 and brzt00 like '4%';
  if v_BRZT00>0 then --已出院病人不生成新的医生组信息
     return;
   end if;
  if nvl(:NEW.YSZID0,0) = 0 then
    return;
  end if;  
  select  B.YSZID0,B.YSZMC0 into v_YSZID0,v_YSZMC0 from zs_ysz000 B where  YSZID0 = :NEW.YSZID0 and rownum<=1;
   
  if v_YSZID0 <> 0 then
          select count(1), nvl(max(DQKSBH),0) into COUNTER, v_OKSBH0 from zs_blxx01 where zyid00 = :new.zyid00 and BLZT00<>'4';
          if COUNTER = 0 then
              :NEW.YSZID0 := v_YSZID0;
          else
              --转科或转组              
              select BMMC00 into v_OKSMC0 from BM_BMBM00 where BMBH00 = v_OKSBH0;
              select BMMC00 into v_NKSMC0 from BM_BMBM00 where BMBH00 = :NEW.DQKS00;
              select YSZBH0,YSZMC0 into v_OYSZID, v_OYSZMC from zs_blxx01 where zyid00 = :new.zyid00 and BLZT00<>'4';
              if v_OYSZID = v_YSZID0 then
                  return;
              end if;
              update ZS_BLXX01 SET YKSBH0=v_OKSBH0,YYSZBH=v_OYSZID,YYSZMC=v_OYSZMC,DQKSBH=:NEW.DQKS00,
                        YSZBH0=v_YSZID0,YSZMC0=v_YSZMC0, BLZT00='2' WHERE ZYID00 = :new.zyid00;
               commit;         
              update ZS_BLLD00 set ZCKSBH=v_OKSBH0,ZCKSMC=v_OKSMC0,ZCYSZB=v_OYSZID,
                     ZCYSZM=v_OYSZMC,ZCCZRQ=to_char(sysdate,'YYYYMMDD'),ZCCZSJ=to_char(sysdate,'HH24:MI:SS'),ZCCZY0='0',
                     ZCCZYX='系统',ZCBZ00='1' where ZYID00=:new.zyid00 and ZCCZRQ='20991231';
               commit;   
              --插入新的流动表       
              insert into ZS_BLLD00(LSID00,ZYID00,ZRKSBH,ZRKSMC,ZRYSZB,ZRYSZM,ZRCZRQ,
                             ZRCZSJ,ZRBZ00,ZRCZY0,ZRCZYX,QKSBH0,QKSMC0,QYSZBH,QYSZMC)
               values (SQ_ZS_BLLD00_LSID00.nextval,:NEW.ZYID00,:NEW.DQKS00,v_NKSMC0,v_YSZID0,v_YSZMC0,to_char(sysdate,'YYYYMMDD'),
                    to_char(sysdate,'HH24:MI:SS'),'1',0,'系统',v_OKSBH0,v_OKSMC0,v_OYSZID,v_OYSZMC);
              commit;
          end if;
  end if;
End;