An exit handler is being ignored by my stored procedure. I execute the following line:
CALL updateTemplate('MyObject', 'NewTemplate');
And receive the error:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (
db.objects, CONSTRAINTObject: Template Foreign KeyFOREIGN KEY (TemplateId) REFERENCEStemplates(TemplateId) ON UPDATE CASCADE)
Indeed, a constraint does fail, because 'NewTemplate' does not exist in the template table. But why do I not receive the custom error message in the exit handler of the stored procedure below?
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateTemplate`(in inObjectId varchar(45), in inTemplateId varchar(45))
BEGIN
declare exit handler for 1452
begin
signal sqlstate '45000' set MESSAGE_TEXT = 'The template identifier is invalid.';
end;
UPDATE objects SET TemplateId=inTemplateId WHERE ObjectId=inObjectId;
END
For info, I'm running MySQL 5.6.20.