COUNT(*) causes NO_DATA_FOUND exception
The output of this trigger is the following:
BEFORE SELECT102END
***SHOULD NOT GET HERE!***
Declare
*
ERROR at line 1:
ORA-20921: STUDENT CANNOT ACCESS THE COURSE
ORA-06512: at "LOAD_ACTIVITY", line 218
ORA-06512: at "LOAD_ACTIVITY", line 261
ORA-06512: at line 19
Here is the code...HOW CAN COUNT(*) CAUSE NO_DATA_FOUND???
CREATE OR REPLACE TRIGGER check_student_grp
BEFORE INSERT
ON course_activity FOR EACH ROW
DECLARE
v_vDPECError dpec_errors_table.error_description%TYPE;
v_nDPECErrorCode dpec_errors_table.error_code%TYPE;
v_vDPECAction dpec_errors_table.error_action%TYPE;
v_count NUMBER;
BEGIN
dbms_output.put_line('BEFORE SELECT'||:new.stuid||'END');
SELECT count(*)
INTO v_count
FROM student_grps s,hub h
WHERE s.stuid = :new.stuid
AND h.gid = s.gid;
dbms_output.put_line('AFTER SELECT'||:new.stuid||'END');
IF v_count = 0 then
RAISE dpec_errors.STUDENT_GRP_INVALID;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('***SHOULD NOT GET HERE!***');
RAISE;
WHEN dpec_errors.STUDENT_GRP_INVALID THEN
dpec_errors.get_error('STUDENT_GRP_INVALID',v_nDPECErrorCode,v_vDPECError,v_vDPECAction);
RAISE_APPLICATION_ERROR(v_nDPECErrorCode,v_vDPECError);
END;
/
ANY IDEAS???
TIA,
RICH