CREATE OR REPLACE TRIGGER tr_patientdiagnose_bins
 BEFORE
  INSERT OR DELETE OR UPDATE
 ON bljk_patientdiagnose
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
declare  ls_cou number(5);
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm      2013.07.29     created;
---------------------------------------------------------
begin
  if inserting or updating then
    select count(*) into ls_cou from BQ_BRZDXX where ZYID00=:new.IPID and ZDLB00=:new.DiagnoseClass and trim(ZDM000)=:new.DiagnoseCode ;
    --select count(*) into ls_cou from BQ_BRZDXX where ZYID00=:new.IPID  and trim(ZDM000)=:new.DiagnoseCode ;
  end if;  
  if inserting and ls_cou=0 then
    insert into BQ_BRZDXX(ID0000,BRID00,ZYID00,ZDLB00,ZDM000,ZDMC00,ZDRQ00,ZDSJ00,SFYZ00,ZLXG00,LRR000,BZ0000,ZDFS00,RYBQ00,FZBM00,FZMC00)
    select sq_bq_brzdxx_id0000.nextval,:new.PID,:new.IPID,:new.DiagnoseClass,:new.DiagnoseCode,substrb(:new.DiagnoseName,1,48),:new.DiagnoseDate,:new.DiagnoseTime,:new.SuspectedInd,:new.TreatResult
           ,(select YGBH00 from BM_YGBM00 where XKH000=:new.Creator)
           ,:new.Remark,:new.DiagnoseType,:new.StateOfIllness,:new.SubDiagnoseCode,:new.SubDiagnoseName 
           from dual;
    
    
  elsif updating then
    update BQ_BRZDXX set ZDM000=:new.DiagnoseCode,
                         ZDMC00=substrb(:new.DiagnoseName,1,48),
                         ZDRQ00=:new.DiagnoseDate,
                         ZDSJ00=:new.DiagnoseTime,
                         SFYZ00=:new.SuspectedInd,
                         ZLXG00=:new.TreatResult,
                         BZ0000=:new.Remark,
                         ZDFS00=:new.DiagnoseType,
                         RYBQ00=:new.StateOfIllness,
                         FZBM00=:new.SubDiagnoseCode,
                         FZMC00=:new.SubDiagnoseName
                  where  BRID00=:new.PID and ZYID00=:new.IPID and trim(ZDM000)=:Old.DiagnoseCode and ZDMC00=:OLD.DiagnoseName;
   elsif deleting then
     delete  BQ_BRZDXX where     BRID00=:old.PID and ZYID00=:old.IPID and trim(ZDM000)=:Old.DiagnoseCode and ZDMC00=:OLD.DiagnoseName and ZDLB00=:OLD.DiagnoseClass;
  end if;  
Exception
  when others        then
    raise_application_error(-20002,substr(nvl(sqlerrm, '原因不明出错')||'！*',1,220));
End tr_PatientDiagnose_bins;
/