From: Ferindo Middleton Jr
Date: January 2 2006 6:24pm
Subject: problem with TRIGGER, unresponsive
List-Archive: http://lists.mysql.com/mysql/193421
Message-Id: <43B96FE0.4000406@verizon.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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;
//