Re: Need help with a trigger

tboots <tboots_at_tez.net>ÀÌ(°¡) ¾Æ·¡ ¸Þ½ÃÁö¸¦ news:GC0Y3.813$3p5.41158_at_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.
>
>
>
>

I think substr() function has three args but in above exam it has only two args like below:

SUBSTR(S1.START_TIME 4, 2)
A blank is between START_TIME and 4 , and you must replace blank to comma. SUBSTR(S1.START_TIME, 4, 2) Have a good time!!! Received on Mon Nov 15 1999 - 19:39:35 CST