Need help with a trigger

From: tboots <tboots_at_tez.net>
Date: Mon, 15 Nov 1999 18:29:31 -0800
Message-ID: <GC0Y3.813$3p5.41158@reader1.interactive.net>

What is wrong with the following trigger, and if possible what might be a better way of writing it :
CREATE OR REPLACE TRIGGER STUDENT BEFORE INSERT ON SCHEDULE1 FOR EACH ROW DECLARE

V_START1 NUMBER;
V_END1 NUMBER;
V_START2 NUMBER :=2;
V_END2 NUMBER :=1;

CURSOR FUBARD IS
SELECT (TO_NUMBER(SUBSTR(S1.START_TIME 1, 1)) + (TO_NUMBER(SUBSTR(S1.START_TIME 4, 2)) * .01)),
(TO_NUMBER(SUBSTR(S1.END_TIME 1, 1)) + (TO_NUMBER(SUBSTR(S1.END_TIME 4, 2))
* .01)),
(TO_NUMBER(SUBSTR(S2.START_TIME 1, 1)) + (TO_NUMBER(SUBSTR(S2.START_TIME 4,
2)) * .01)),
(TO_NUMBER(SUBSTR(S2.END_TIME 1, 1)) + (TO_NUMBER(SUBSTR(S2.END_TIME 4, 2))
* .01))

FROM SCHEDULE1 S1, SHEDULE1 S2, STUDENT_SCHEDULE1 WHERE STUDENT_SCHEDULE1.STUDENT_ID = :NEW.STUDENT_ID,

 AND S1.SCHEDULE_ID = STUDENT_SCHEDULE1.SCHEDULE_ID
 AND S1.TERM_CODE = :NEW.TERM_CODE
 AND S2.TERM_CODE = :NEW.TERM_CODE
 AND S2.DAY = S1.DAY;

BEGIN
OPEN FUBARD;
LOOP
FETCH FUBARD INTO V_START1, V_END1, V_START2, V_END2; EXIT WHEN V_START2 = 2;
IF (V_START1 = V_SART2 OR V_START1 > V_START2) AND (V_START1 = V_END2 OR V_START1 < V_END2)
 THEN RAISE_APPLICATION_ERROR (-20001, 'OVERLAPING TIME PERIOD'); ELSIF (V_END1 = V_START2 OR V_END1 > V_SART2) AND (V_END1 = V_END2 OR V_END1 < V_END2)
 THEN RAISE_APPLICATION_ERROR (-20001, 'OVERLAPING TIME PERIOD'); END IF;
END LOOP;
CLOSE FUBARD
END;
giving that student_schedule1 has 4 fields

     student_id. schedule_id, term_code, and student_grade and schedule1 is composed of

    schedule_id, term_code, day, start_time, end_time, and room both start & end_time are date_fields.
The triggers objective is to keep a student from registering for a class that takes place during the same time as one he has already registered for. thanks for the help. Received on Mon Nov 15 1999 - 20:29:31 CST