1

I need to set up a trigger in MariaDB 10.1 which inserts rows in another table. So far the trigger looks like this for a single record:

DELIMITER //
CREATE TRIGGER generate_items
    AFTER INSERT
    ON my_table
    FOR EACH ROW
BEGIN
    -- some_id is the id from my_table
    INSERT INTO another_table(some_id, some_value)
    VALUES (NEW.id, 'x');
END;
//

DELIMITER ;

My problem is that some_value is not the only record to be inserted but a list of values fetched from another table. Actually I would need something like this in the trigger:

-- Pseudo-code for illustrating
SET @values = SELECT value FROM some_table WHERE condition = true;
FOREACH(@value in @values) DO
  INSERT INTO another_table(some_id, some_value) VALUES (NEW.id, @value);
END

Which possibilities would I have with MariaDB 10.1? I read that MariaDB 10.3 offers a FOR-loop but we cannot upgrade the database yet.

GMB
  • 216,147
  • 25
  • 84
  • 135
Robert Strauch
  • 12,055
  • 24
  • 120
  • 192
  • 4
    Possible duplicate of [MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?](https://stackoverflow.com/questions/9422529). You should *not* need a "for loop". You should be able to use " insert into ..select.." syntax instead, per the link. – paulsm4 Oct 29 '19 at 20:04

1 Answers1

1

You can use the insert ... select syntax instead of insert ... values(). So something like:

CREATE TRIGGER generate_items
    AFTER INSERT ON my_table FOR EACH ROW
    INSERT INTO another_table(some_id, some_value)
    SELECT NEW.id, value FROM some_table WHERE condition = true
;

This will insert as many records in another_table as there are in some_table for the given conditions, retrieving value from the query while assigning the same NEW.id to every record.

Side note: since the trigger is made of a single query, there is no need to use BEGIN and END, nor to change the delimiter. The above simplified version should just work.

GMB
  • 216,147
  • 25
  • 84
  • 135