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_attendance.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_attendance 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_attendance.
delimiter //
CREATE TRIGGER trigger_registration_and_attendance_before_insert
BEFORE INSERT
ON registration_and_attendance
FOR EACH ROW
BEGIN
DECLARE schedule_class_id, b INT;
DECLARE schedule_class_id_cursor 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_cursor;
REPEAT
FETCH schedule_class_id_cursor INTO schedule_class_id;
UNTIL b = 1 /* I wonder if this loop is even necessary because
schedule_class_id_cursor should only return one value anyway */
END REPEAT;
CLOSE schedule_class_id_cursor;
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/[EMAIL PROTECTED]
