CREATE OR REPLACE TRIGGER TR_WAIT_STATUS_CHANGE
  AFTER UPDATE OF WAIT_STATUS ON SPACS.EXAM_QUEUE 
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
--cOperate	VARCHAR2(32);               --操作名称
--cModule   VARCHAR2(10);               --模块名称
cDiscription		VARCHAR2(50);         --描述

cSickID   VARCHAR2(20);       --病人ID
cExamClass VARCHAR2(20);      --检查类别
cScheduleDate VARCHAR2(10);   --预约日期
--cExamNoNew VARCHAR2(12);   --
cExamNoOld VARCHAR2(12);   --
--cExamNoTmp VARCHAR2(12);   --
cWaitStatusNew VARCHAR2(12);   --

--strSQL VARCHAR2(500);   --
BEGIN
	--参数
  cWaitStatusNew := :NEW.WAIT_STATUS;
  cExamNoOld := :OLD.EXAM_NO;
 -- cOperate:='呼叫病人';
--	cModule:='CALLLIB';
  --cDiscription:='呼叫同名病人';

	IF :NEW.WAIT_STATUS = '2' THEN
     Select SICK_ID,EXAM_CLASS,SCHEDULED_DATE INTO cSickID,cExamClass,cScheduleDate From v_Exams Where EXAM_NO = :OLD.EXAM_NO;


     Begin
         Update SPACS.EXAM_QUEUE SET WAIT_STATUS = '4',CALL_TIME = TO_CHAR(sysdate,'HH24:MI:SS') where exam_no in
         (
               Select V_Exams.Exam_No From V_Exams,Exam_Queue 
               Where V_Exams.Exam_No = Exam_Queue.Exam_No And SICK_ID = cSickID And V_EXAMS.EXAM_CLASS = cExamClass 
               And v_Exams.SCHEDULED_DATE = cScheduleDate And WAIT_STATUS <'2' And V_Exams.exam_no !=:OLD.EXAM_NO
         )
         ;
      Exception 
      When others then
         cDiscription:='呼叫同名病人';
         COMMIT; 
      End;
  COMMIT;   
	END IF;
  

END TR_WAIT_STATUS_CHANGE;