-- Start of DDL Script for Trigger SD_HOSPITAL.TR_IC_YHXX00_ZT0000
-- Generated 18-九月-2023 18:35:08 from SD_HOSPITAL@njxyy

CREATE OR REPLACE TRIGGER tr_ic_yhxx00_zt0000
 BEFORE
  INSERT OR DELETE OR UPDATE
 ON ic_yhxx00
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
declare
  ls_error   varchar2(100);
  Ecustom    EXCEPTION;
  ls_count   number;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CX         2017.02.15  create for    MZSF-20170207-002
-- dsm        2017.06.06  归档 for MZSF-20170419-002
begin
  if deleting and :old.ZT0000='1' then
    delete IC_YHXXZT where BRID00=:OLD.BRID00 and ZT0000='1' and ickh00=:old.ickh00;
  elsif updating and (:new.zt0000='1' or :old.zt0000='1')  and :new.ickh00 is not null and  :new.brid00 is not null  and trim(:new.ickh00)<>trim(:old.ickh00)  then    
    select count(*) into ls_count from IC_YHXXZT where brID00=:new.brID00 and ickh00=:new.ickh00;
    if ls_count>0 and :new.zt0000='1' then
        update IC_YHXXZT set
         ickh00=:new.ickh00,
         brid00=:new.brid00,
         Zt0000=:new.Zt0000
          where brID00=:OLD.brID00 and ickh00=:old.ickh00;  
    elsif ls_count>0 and :new.zt0000<>'1'  then
         delete IC_YHXXZT where brID00=:OLD.brID00 and ickh00=:old.ickh00;
    elsif ls_count=0    and :new.zt0000='1'  then
      insert into IC_YHXXZT(brID00,ickh00,zt0000)
      values(:new.brID00,:new.ickh00,:new.Zt0000);
    end if;  
  elsif inserting    and :new.zt0000='1'  and :new.ickh00 is not null and :new.brid00 is not null then
    insert into IC_YHXXZT(brID00,ickh00,zt0000) values(:new.brID00,:new.ickh00,:new.Zt0000);
  end if;
exception
  when Ecustom       then
    raise_application_error(-20010,substr(ls_error||'！*',1,500));
  when others        then
    if instr(sqlerrm,'uq_ic_yhxxzt_brid_zt')>0 then
        raise_application_error(-20002,substr('每人只能有一个有效状态的卡！'||'！*',1,500)); 
    else
      raise_application_error(-20002,substr(nvl(sqlerrm, '原因不明出错')||'！*',1,500));
    end if;  
end;
/


-- End of DDL Script for Trigger SD_HOSPITAL.TR_IC_YHXX00_ZT0000