title: Mysql loop trigger
body: <p>I'm trying to do Closure tables with parentId so I have two tables (Subjects and SubjectsTree). When I insert a record in Subjects I need to add records in SubjectsTree for every Subjects.Id = new.ParentId. I'm trying in that way:</p>
  
  <pre><code>delimiter //
  CREATE TRIGGER test AFTER INSERT ON Subjects
  FOR EACH ROW
  BEGIN
      DECLARE done INT DEFAULT FALSE;
      DECLARE ids INT;
      DECLARE cur CURSOR FOR SELECT Id FROM Subjects inner join SubjectsTree on SubjectsTree.DescendantId = new.ParentId;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
      OPEN cur;
          ins_loop: LOOP
              FETCH cur INTO ids;
              IF done THEN
                  LEAVE ins_loop;
              END IF;
              INSERT INTO SubjectsTree VALUES (ids, new.Id);
          END LOOP;
      CLOSE cur;
      insert into SubjectsTree values (new.Id, new.Id);
  END; //
  </code></pre>
  
  <p>But it doesn't work just because it repeats for all records in Subjects! And when I try without loop it doesn't.</p>
  
  <p>Why does this code aren't executed for each record in Subjects?</p>
  
  <p>create trigger test after insert on Subjects
  for each row
      insert into some_test_table values ('test value');</p>
  
  <p>And how can I solve this problem? Or maybe there is a better way?</p>
  
  <pre><code>Subjects table: 
     Id int PRI
     Name longtext
     ParentId int
  
  SubjectsTree table:
     AncestorId int PRI
     DescendantId int PRI
  </code></pre>