CREATE OR REPLACE TRIGGER t_UserLessons_Update
AFTER DELETE or INSERT or UPDATE OF
Status
on UserLessons for each row
declare
M_FactDuration NUMBER(13,2) DEFAULT 0;
M_OffDuration NUMBER(13,2) DEFAULT 0;
M_CreditHours NUMBER(13,2) DEFAULT 0;
begin
--判断是否已经完成学习任务,如果完成更新用户合同状态
BEGIN
--已完成的计划课程学分
/*
SELECT SUM(Duration)/60
INTO M_FactDuration
FROM UserLessons
WHERE Year=:new.Year AND UserID=:new.UserID AND PlanLessonChk=1 AND Status='F' AND PassChk=1; EXCEPTION WHEN NO_Data_Found THEN
M_FactDuration := 0;
END;
*/
BEGIN
--合同签订需要完成的学分
SELECT a.CreditHours
INTO M_CreditHours
FROM LessonContracts a
WHERE a.Year=:new.Year AND a.UserID=:new.UserID AND NVL(a.DelChk,0)=0 AND a.Status='O' AND ROWNUM=1
ORDER BY ContractID DESC;
EXCEPTION WHEN NO_Data_Found THEN
M_CreditHours := 10000;
END;
UPDATE LESSONCONTRACTS
SET FINISHEDDURATION=M_FactDuration + M_OffDuration
WHERE Year=:new.Year AND UserID=:new.UserID; IF (M_FactDuration + M_OffDuration) > NVL(M_CreditHours,10000) THEN
UPDATE LESSONCONTRACTS
SET STATUS='P'
, CLOSEDATE=SYSDATE
WHERE Year=:new.Year AND UserID=:new.UserID;
END IF;
end;
以上是触发器内容,运行注释中部分的内容就报错,看问题似乎只要查询UserLessons表就会报错,请问是何故?
AFTER DELETE or INSERT or UPDATE OF
Status
on UserLessons for each row
declare
M_FactDuration NUMBER(13,2) DEFAULT 0;
M_OffDuration NUMBER(13,2) DEFAULT 0;
M_CreditHours NUMBER(13,2) DEFAULT 0;
begin
--判断是否已经完成学习任务,如果完成更新用户合同状态
BEGIN
--已完成的计划课程学分
/*
SELECT SUM(Duration)/60
INTO M_FactDuration
FROM UserLessons
WHERE Year=:new.Year AND UserID=:new.UserID AND PlanLessonChk=1 AND Status='F' AND PassChk=1; EXCEPTION WHEN NO_Data_Found THEN
M_FactDuration := 0;
END;
*/
BEGIN
--合同签订需要完成的学分
SELECT a.CreditHours
INTO M_CreditHours
FROM LessonContracts a
WHERE a.Year=:new.Year AND a.UserID=:new.UserID AND NVL(a.DelChk,0)=0 AND a.Status='O' AND ROWNUM=1
ORDER BY ContractID DESC;
EXCEPTION WHEN NO_Data_Found THEN
M_CreditHours := 10000;
END;
UPDATE LESSONCONTRACTS
SET FINISHEDDURATION=M_FactDuration + M_OffDuration
WHERE Year=:new.Year AND UserID=:new.UserID; IF (M_FactDuration + M_OffDuration) > NVL(M_CreditHours,10000) THEN
UPDATE LESSONCONTRACTS
SET STATUS='P'
, CLOSEDATE=SYSDATE
WHERE Year=:new.Year AND UserID=:new.UserID;
END IF;
end;
以上是触发器内容,运行注释中部分的内容就报错,看问题似乎只要查询UserLessons表就会报错,请问是何故?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货