下面这个触发器报
TRIGGER SYSTEM.TRI_MRONLINE_DOC 编译错误
错误:PLS-00103: Encountered the symbol ";" when expecting one of the following:
if 行:22 文本:END;
大家帮忙找找,看看错在哪里,谢谢!
CREATE OR REPLACE TRIGGER TRI_MRONLINE_DOC
AFTER INSERT OR UPDATE ON MR_ON_LINE
FOR EACH ROW
DECLARE I NUMBER;
BEGIN
SELECT COUNT(*) INTO I FROM MR_PAT_DOCTORS WHERE PATIENT_ID=:NEW.PATIENT_ID AND VISIT_ID=:NEW.VISIT_ID;
IF INSERTING THEN
IF I=0 THEN
INSERT INTO MR_PAT_DOCTORS(PATIENT_ID,VISIT_ID,STATUS,REQUEST_DOCTOR_ID,REQUEST_DATE_TIME,PARENT_DOCTOR_ID,SUPER_DOCTOR_ID)
VALUES(:NEW.PATIENT_ID,:NEW.VISIT_ID,:NEW.STATUS,:NEW.REQUEST_DOCTOR_ID,:NEW.REQUEST_DATE_TIME,:NEW.PARENT_DOCTOR_ID,:NEW.SUPER_DOCTOR_ID);
END IF;
ELSE IF UPDATEING THEN
IF I=0 THEN
INSERT INTO MR_PAT_DOCTORS(PATIENT_ID,VISIT_ID,STATUS,REQUEST_DOCTOR_ID,REQUEST_DATE_TIME,PARENT_DOCTOR_ID,SUPER_DOCTOR_ID)
VALUES(:NEW.PATIENT_ID,:NEW.VISIT_ID,:NEW.STATUS,:NEW.REQUEST_DOCTOR_ID,:NEW.REQUEST_DATE_TIME,:NEW.PARENT_DOCTOR_ID,:NEW.SUPER_DOCTOR_ID);
ELSE
UPDATE MR_PAT_DOCTORS SET STATUS=:NEW.STATUS,REQUEST_DOCTOR_ID=:NEW.REQUEST_DOCTOR_ID,
REQUEST_DATE_TIME=:NEW.REQUEST_DATE_TIME,PARENT_DOCTOR_ID=:NEW.PARENT_DOCTOR_ID,
SUPER_DOCTOR_ID=:NEW.SUPER_DOCTOR_ID WHERE PATIENT_ID=:NEW.PATIENT_ID AND VISIT_ID=:NEW.VISIT_ID;
END IF;
END IF;
END;
找到原因了,ELSE IF UPDAT... 去掉ELSE 好了。
此外触发器创建者角色没有对MR_PAT_DOCTORS 表进行操作的权限也有问题,还有updating写成了updateing了。
数据库脚本真的得细心再细心。。。