Ferindo Middleton Jr wrote:
Gleb Paharenko wrote:
Hello.
It seems that you forgot to OPEN the cursor. The trigger should be
similar to this one:
CREATE TRIGGER trigger_registration_and_attendance_before_insert
BEFORE INSERT
ON registration_and_attendance
FOR EACH ROW
BEGIN
DECLARE schedule_class_id INT;
DECLARE schedule_class_id_cursor CURSOR FOR
SELECT class_id
FROM schedules
WHERE schedules.id = new.schedule_id;
OPEN schedule_class_id_cursor;
FETCH schedule_class_id_cursor INTO schedule_class_id;
SET new.class_id = schedule_class_id;
CLOSE schedule_class_id_cursor ;
END;
Ferindo Middleton Jr wrote:
Is it possible to SET values on fields that involve the TABLE that
invoked the TRIGGER with SET actions.
I have the following lines in my trigger:
delimiter //
CREATE TRIGGER trigger_registration_and_attendance_before_insert
BEFORE INSERT
ON registration_and_attendance
FOR EACH ROW
BEGIN
DECLARE schedule_class_id INT;
DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM
schedules WHERE schedules.id =
new.schedule_id;
FETCH schedule_class_id_cursor INTO schedule_class_id;
SET new.class_id = schedule_class_id;
END;
The server accepts this but "new.class_id" doesn't get a value when I do
an INSERT. Why won't this work?
Ferindo
Hi,
I tried the code above, opening and the cursor before assigning the
value called from the declaration into new.class_id but it still doesn't
work. The class_id field isn't picking up the value it should from my
schedules table. I can't figure out why - frustrating this. Thanks.
Ferindo
Hello again Gleb,
After further testing I have determined that my statement above is
incorrect... The trigger above is being evaluated... well SORT OF. The
class_id field is part of the primary key of registration_attendance
table. With the application I developed to load data into these tables,
if I do something like force an arbitrary value for the class_id field
within the application, the system works on INSERTs and the trigger
appears to be executed -overwriting- whatever value I manually hard
coded into the application to be passed to the class_id field.
As you can see this trigger is supposed to happen BEFORE INSERT but it
appears data from my application is being evaluated into the table
before the trigger fires. I guess I could just force an arbitrary value
on the field as a workaround but isn't this a flaw. Shouldn't the
trigger be executed before the database evaluates data against the table?
I've used a trigger similar to this in Postgresql and the Postgresql db
wouldn't introduce the data to the table until after the trigger
executes which is how it should be. This appears to be a flaw in the
MySQL implementation of TRIGGER implementation BEFORE INSERT.
It appears that in MySQL, what it may be doing is:
1. Evaluate the data against the table although not committing the
INSERT data
2. Execute the BEFORE INSERT TRIGGER
3. Then actually INSERT the data
- When I should Perform Step 2 from above, Executing the Trigger before
beginning any evaluation of the data into the database table.
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
