Local variable in CREATE Triggger statement causes MySQL to crash
Posted by:
KC Kim
Date: August 20, 2005 10:48PM
I have the problem that if I were to user a local variable varboardtype as described below (which compiles successfully), when the trigger is called, some error occurs causing MySQL to be forcibly shutdown.
delimiter //
CREATE TRIGGER trg_update_prdtboard AFTER UPDATE ON prdtboard
FOR EACH ROW
BEGIN
DECLARE varboardtype varchar(10) DEFAULT '';
SELECT boardtype.type INTO varboardtype FROM boardtype WHERE boardtype.id = NEW.board_id;
UPDATE prdt SET description = concat(NEW.thickness, 'mm x ', NEW.width, '\' x ', NEW.length, '\' - ', varboardtype) WHERE prdt.id = NEW.prdt_id;
END;//
delimiter ;
BUT, if I used a system variable, as given below, it works fine. I want to use a local variable for this trigger, please advise.
delimiter //
CREATE TRIGGER trg_update_prdtboard AFTER UPDATE ON prdtboard
FOR EACH ROW
BEGIN
SELECT boardtype.type INTO @varboardtype FROM boardtype WHERE boardtype.id = NEW.board_id;
UPDATE prdt SET description = concat(NEW.thickness, 'mm x ', NEW.width, '\' x ', NEW.length, '\' - ', @varboardtype) WHERE prdt.id = NEW.prdt_id;
END;//
delimiter ;
Edited 1 time(s). Last edit at 08/20/2005 10:50PM by KC Kim.