MySQL Forums
Forum List  »  Triggers

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Local variable in CREATE Triggger statement causes MySQL to crash
4979
August 20, 2005 10:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.