CREATE OR REPLACE TRIGGER TR_SF_YSPBB0_ALL AFTER INSERT OR DELETE OR UPDATE OF YSZC00, YSKSBH, YSPBXQ, YSMZLB, YSYGBH, TZBZ00, YSPBXH, YSYYH0, YSPBSJ, YSZKBH ON SD_SF.SF_YSPBB0 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare -- local variables here VS_PROCESS XT_XTRZ00.JCID00%type; VS_YGBH00 XT_XTRZ00.YGBH00%type; VD_CZID00 number; VS_CZNR00 XT_XTCZRZ.CZNR00%type; VS_KSMC00 BM_BMBM00.BMMC00%type; VS_ZKMC00 BM_ZKBM00.ZKMC00%type; VS_YSMC00 BM_YGBM00.ZWXM00%type; VS_XQ number; -- MODIFICATION HISTORY -- Person Date Comments -- daihq 2012.09.12 增加sf_yspbb0医生排班表开诊、停诊操作日志 -- royal 2016.07.06 增加判断开诊，停诊操作日志 for YYGH-20160518-001 begin -- If DELETING OR UPDATING OR INSERTING select userenv('SESSIONID') PROCESS into VS_PROCESS from dual where rownum=1; begin select YGBH00 into VS_YGBH00 from XT_XTRZ00 where JCID00=VS_PROCESS and LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS); exception when others then VS_YGBH00:=0; end; select SQ_XT_XTCZRZ_ID0000.NEXTVAL into VD_CZID00 from dual where ROWNUM=1; if INSERTING Then select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00=:NEW.YSKSBH and ROWNUM=1; If :NEW.YSZKBH is not null then select ZKMC00 into VS_ZKMC00 from BM_ZKBM00 where ZKBM00=:NEW.YSZKBH and ROWNUM=1; end if; select ZWXM00 into VS_YSMC00 from BM_YGBM00 where YGBH00=:NEW.YSYGBH And ROWNUM=1; VS_CZNR00 := VS_CZNR00||'科室:'||trim((VS_KSMC00))||';'; VS_CZNR00 := VS_CZNR00||'医生:'||trim((VS_YSMC00))||';'; VS_CZNR00 := VS_CZNR00||'挂号类别:'||trim((:NEW.YSMZLB))||';'; VS_CZNR00 := VS_CZNR00||'地点:'||trim((:NEW.YSZC00))||';'; VS_CZNR00 := VS_CZNR00||'专科:'||trim((VS_ZKMC00))||';'; if :NEW.YSPBXQ=1 then VS_XQ := 8; else VS_XQ := :NEW.YSPBXQ; end if; VS_CZNR00 := VS_CZNR00||'星期:'||trim(TO_CHAR(VS_XQ-1))||';'; VS_CZNR00 := VS_CZNR00||'门诊时间:'||trim(:NEW.YSPBSJ)||';'; VS_CZNR00 := VS_CZNR00||'挂号限号:'||trim(TO_CHAR(:NEW.YSPBXH))||';'; VS_CZNR00 := VS_CZNR00||'预约限号:'||trim(TO_CHAR(:NEW.YSYYH0))||';'; VS_CZNR00 := VS_CZNR00||'排班类型:'||trim(TO_CHAR(:NEW.PBLX00))||';'; --VS_CZNR00 := VS_CZNR00||'预约限号明细:'||';'; insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) values(VD_CZID00,'SF_YSPBB0',0,VS_YGBH00,VS_CZNR00,:NEW.YSPBID); elsif DELETING then select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00=:OLD.YSKSBH and ROWNUM=1; if :OLD.YSZKBH is not null then select ZKMC00 into VS_ZKMC00 from BM_ZKBM00 where ZKBM00=:OLD.YSZKBH and ROWNUM=1; end if; select ZWXM00 into VS_YSMC00 from BM_YGBM00 where YGBH00=:OLD.YSYGBH and ROWNUM=1; VS_CZNR00 := VS_CZNR00||'科室:'||trim((VS_KSMC00))||';'; VS_CZNR00 := VS_CZNR00||'医生:'||trim((VS_YSMC00))||';'; VS_CZNR00 := VS_CZNR00||'挂号类别:'||trim((:OLD.YSMZLB))||';'; VS_CZNR00 := VS_CZNR00||'地点:'||trim((:OLD.YSZC00))||';'; VS_CZNR00 := VS_CZNR00||'专科:'||trim((VS_ZKMC00))||';'; if :OLD.YSPBXQ=1 then VS_XQ := 8; else VS_XQ := :OLD.YSPBXQ; end if; VS_CZNR00 := VS_CZNR00||'星期:'||trim(to_char(VS_XQ-1))||';'; VS_CZNR00 := VS_CZNR00||'门诊时间:'||trim(:OLD.YSPBSJ)||';'; VS_CZNR00 := VS_CZNR00||'挂号限号:'||trim(to_char(:OLD.YSPBXH))||';'; VS_CZNR00 := VS_CZNR00||'预约限号:'||trim(to_char(:OLD.YSYYH0))||';'; VS_CZNR00 := VS_CZNR00||'排班类型:'||trim(TO_CHAR(:OLD.PBLX00))||';'; --VS_CZNR00 := VS_CZNR00||'预约限号明细:'||';'; insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) values(VD_CZID00,'SF_YSPBB0',2,VS_YGBH00,VS_CZNR00,:OLD.YSPBID); elsif UPDATING then if (:OLD.YSKSBH<>:NEW.YSKSBH)Or(:OLD.YSZKBH<>:NEW.YSZKBH)Or(:OLD.YSYGBH<>:NEW.YSYGBH) Or(:OLD.YSMZLB<>:NEW.YSMZLB)Or(:OLD.YSZC00<>:NEW.YSZC00)Or(:OLD.YSPBXQ<>:NEW.YSPBXQ) Or(:OLD.YSPBSJ<>:NEW.YSPBSJ)Or(:OLD.YSPBXH<>:NEW.YSPBXH)Or(:OLD.YSYYH0<>:NEW.YSYYH0) or (:OLD.TZBZ00<>:NEW.TZBZ00) or (:OLD.PBLX00<>:NEW.PBLX00) then select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00=:OLD.YSKSBH and ROWNUM=1; VS_CZNR00 := VS_CZNR00||'科室:'||trim(VS_KSMC00); if :OLD.YSKSBH<>:NEW.YSKSBH then select BMMC00 into VS_KSMC00 from BM_BMBM00 where BMBH00=:NEW.YSKSBH and ROWNUM=1; VS_CZNR00 := VS_CZNR00||'->'||trim(VS_KSMC00); end if; VS_CZNR00 := VS_CZNR00||';'; select ZWXM00 into VS_YSMC00 from BM_YGBM00 Where YGBH00=:OLD.YSYGBH and ROWNUM=1; VS_CZNR00 := VS_CZNR00||'医生:'||trim(VS_YSMC00); if :OLD.YSYGBH<>:NEW.YSYGBH then select ZWXM00 into VS_YSMC00 from BM_YGBM00 Where YGBH00=:NEW.YSYGBH and ROWNUM=1; VS_CZNR00 := VS_CZNR00||'->'||trim(VS_YSMC00); end if; VS_CZNR00 := VS_CZNR00||';'; if :OLD.YSMZLB<>:NEW.YSMZLB then VS_CZNR00 := VS_CZNR00||'挂号类别:'||trim(:OLD.YSMZLB)||'->'||trim(:NEW.YSMZLB)||';'; --Else -- VS_CZNR00 := VS_CZNR00||'挂号类别:'||Trim(:OLD.YSMZLB)||';'; end if; if :OLD.YSZC00<>:NEW.YSZC00 then VS_CZNR00 := VS_CZNR00||'地点:'||trim(:OLD.YSZC00)||'->'||trim(:NEW.YSZC00)||';'; --Else -- VS_CZNR00 := VS_CZNR00||'地点(诊室):'||Trim(:OLD.YSZC00)||';'; end if; if :OLD.YSZKBH<>:NEW.YSZKBH then if :OLD.YSZKBH is not null then select ZKMC00 into VS_ZKMC00 from BM_ZKBM00 Where ZKBM00=:OLD.YSZKBH and ROWNUM=1; end if; VS_CZNR00 := VS_CZNR00||'专科:'||trim((VS_ZKMC00)); VS_ZKMC00 :=''; if :NEW.YSZKBH is not null then select ZKMC00 into VS_ZKMC00 from BM_ZKBM00 Where ZKBM00=:NEW.YSZKBH and ROWNUM=1; end if; VS_CZNR00 := VS_CZNR00||'->'||trim((VS_ZKMC00))||';'; end if; if :OLD.YSPBXQ<>:NEW.YSPBXQ then if :OLD.YSPBXQ=1 then VS_XQ := 8; else VS_XQ := :OLD.YSPBXQ; end if; VS_CZNR00 := VS_CZNR00||'星期:'||Trim(to_char(VS_XQ-1)); if :NEW.YSPBXQ=1 then VS_XQ := 8; else VS_XQ := :NEW.YSPBXQ; end if; VS_CZNR00 := VS_CZNR00||'->'||Trim(to_char(VS_XQ-1))||';'; Else --VS_CZNR00 := VS_CZNR00||'星期:'||Trim(TO_CHAR(:OLD.YSPBXQ-1))||';'; if :OLD.YSPBXQ=1 then VS_XQ := 8; else VS_XQ := :OLD.YSPBXQ; end if; VS_CZNR00 := VS_CZNR00||'星期:'||Trim(to_char(VS_XQ-1))||';'; end if; if :OLD.YSPBSJ<>:NEW.YSPBSJ then VS_CZNR00 := VS_CZNR00||'门诊时间:'||trim(:OLD.YSPBSJ)||'->'||trim(:NEW.YSPBSJ)||';'; else VS_CZNR00 := VS_CZNR00||'门诊时间:'||trim(:OLD.YSPBSJ)||';'; end if; if :OLD.YSPBXH<>:NEW.YSPBXH then VS_CZNR00 := VS_CZNR00||'挂号限号:'||to_char(:OLD.YSPBXH)||'->'||to_char(:NEW.YSPBXH)||';'; --Else -- VS_CZNR00 := VS_CZNR00||'挂号限号数:'||TO_CHAR(:OLD.YSPBXH)||';'; end if; if :OLD.YSYYH0<>:NEW.YSYYH0 then VS_CZNR00 := VS_CZNR00||'预约限号:'||to_char(:OLD.YSYYH0)||'->'||to_char(:NEW.YSYYH0)||';'; --Else -- VS_CZNR00 := VS_CZNR00||'预约限号:'||TO_CHAR(:OLD.YSYYH0)||';'; end if; if :OLD.PBLX00<>:NEW.PBLX00 then VS_CZNR00 := VS_CZNR00||'排班类型:'||to_char(:OLD.PBLX00)||'->'||to_char(:NEW.PBLX00)||';'; end if; if :OLD.TZBZ00<>:NEW.TZBZ00 then VS_CZNR00:=VS_CZNR00||'停诊标志：'||trim(:OLD.TZBZ00)||'->'||trim(:NEW.TZBZ00)||';'; if :NEW.TZBZ00=0 then insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(VD_CZID00,'SF_YSPBB0',3,VS_YGBH00,VS_CZNR00,:OLD.YSPBID); else insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(VD_CZID00,'SF_YSPBB0',4,VS_YGBH00,VS_CZNR00,:OLD.YSPBID); end if; else insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(VD_CZID00,'SF_YSPBB0',1,VS_YGBH00,VS_CZNR00,:OLD.YSPBID); end if; end if; end if; exception when others then RAISE_APPLICATION_ERROR(-20004,'数据没有找到！*'||SQLERRM); end TR_SF_YSPBB0_ALL; /