1

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

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.

|improve this answer|||||
  • It's enough to delete SELECT @itemTypeID; statement from procedure code. – Akina May 22 '18 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.