mysql trigger for loop

Question

I'm trying to do some mysql trigger coding. I fail every time I meet loops.

CREATE TRIGGER `after_insert` AFTER INSERT ON `table_users`
  FOR EACH ROW BEGIN
    INSERT INTO table_user_plan (user_id, plan_id) VALUES 
    (NEW.id, (SELECT id FROM table_plans))
    ;
END

Here trigger is successfully created, but I get error

#1242 - Subquery returns more than 1 row

I understand this cannot work, because there are more than one row in table_plans... but how can I handle this if I want to add multiple rows or how can I make a loop and firstly select plans then insert into table_users?

Thanks in advanced

Answer 1

To insert multiple rows based on a SELECT you would use the INSERT ... SELECT FROM ... syntax. In this case, you would use something like

INSERT INTO table_user_plan
  SELECT NEW.id AS user_id, tp.id AS plan_id
    FROM table_plans tp;

(I think that should work, although I've never actually tried to use NEW in this context.)