MySQL - using result of Procedure in Trigger to Insert in new table

Question

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');

Answer 1

I believe I have found the solution, and that is to convert the Procedure to a Function.

DELIMITER $$
    CREATE FUNCTION fn_get_item_type_id( item_name varchar(45) )
    RETURNS INT DETERMINISTIC
        RETURN (SELECT 
            CASE
                WHEN LEFT(`item_name`,6)  = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = 'type1')
            ELSE 0
            END AS `item_type_id`);
    $$
DELIMITER ;

From there I can simply Select it into a variable in the Trigger.