| Ferindo Middleton Jr 2006-01-02, 1:23 pm |
| I have these two tables: 'registration_and attendance' and 'schedules'
They both share a common class_id field. I'm trying to write a Trigger
which will set the class_id field for 'registration_and attendance'
equal to the schedules.class_id matching the
registration_and_att
endance.schedule_id
The below trigger doesn't return any error message when I try to load it
into my db but the end result should be a value that
registration_and_att
endance table picks up for the class_id matching the
foreign key, schedule_id, the two tables share. However, nothing
happens..... there is no value in 'new.class_id' on INSERTs to
registration_and_att
endance.
delimiter //
CREATE TRIGGER trigger_registration
_and_attendance_befo
re_insert
BEFORE INSERT
ON registration_and_att
endance
FOR EACH ROW
BEGIN
DECLARE schedule_class_id, b INT;
DECLARE schedule_class_id_cu
rsor CURSOR FOR SELECT class_id FROM
schedules WHERE schedules.id = new.schedule_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN schedule_class_id_cu
rsor;
REPEAT
FETCH schedule_class_id_cu
rsor INTO schedule_class_id;
UNTIL b = 1 /* I wonder if this loop is even necessary because
schedule_class_id_cu
rsor should only return one value anyway */
END REPEAT;
CLOSE schedule_class_id_cu
rsor;
SET new.class_id = schedule_class_id;
END;
//
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|