I'm guessing I'm doing something that's prohibited by MySQL, but can't see what.
Basically, I have a set of tables that hold lists of things (item name
, item types
, item groups
etc.), and I need to create a joining table to hold each of the ID's (e.g. item_id
, item_type_id
, item_group_id
). At present there is no real relationship between the various lists. This table needs to be automatically populated each time I add a new item
.
To get the matching entry, I need to use a CASE
statement to match the first letters of the item_name
, and get the relevant entry from the other table(s).
My plan is to have a set of Procedures (one for each table I need to join):
DELIMITER $$
CREATE PROCEDURE pr_get_item_type_id( item_name varchar(45) )
BEGIN
SET @itemTypeID := (SELECT
CASE
WHEN LEFT(`item_name`,6) = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = 'type1')
#repeats multiple times . . .
ELSE 0
END AS `item_type_id`);
SELECT @itemTypeID;
END;
$$
DELIMITER ;
This works fine.
I then have a trigger:
DELIMITER $$
CREATE TRIGGER tr_item_details
AFTER INSERT
ON `item` FOR EACH ROW
BEGIN
SET @itemId := new.`id`;
CALL pr_get_item_type_id(new.`item_name`); #-->this Sets var @itemTypeID
INSERT INTO `item_details` (`item_id`,`item_type_id`) VALUES (@itemID,@itemTypeID);
END;
$$
DELIMITER ;
I then test this as follows:
INSERT INTO item (id,item_name) VALUES (1000,'abcdef')
But this results in:
Error Code: 1415. Not allowed to return a result set from a trigger
I tried setting the variable inside the trigger instead of the procedure:
SET @itemTypeID = (CALL pr_get_item_type_id(new.`item_name`));
But that didn't work either.
Is there a way to do this?
Example Tables:
CREATE TABLE `item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `item_name` varchar(45) NOT NULL DEFAULT 'UNKNOWN', PRIMARY KEY (`id`));
CREATE TABLE `item_type` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `item_type_name` varchar(45) NOT NULL DEFAULT 'UNKNOWN', PRIMARY KEY (`id`));
CREATE TABLE `item_details` (`item_id` int(10) unsigned NOT NULL,`item_type_id` int(10) unsigned NOT NULL,`item_group_id` int(10) unsigned NOT NULL,PRIMARY KEY (`item_id`,`item_type_id`,`item_group_id`));
INSERT INTO `item_type` (`item_type_name`) VALUES ('type1'),('type2');