title: MySQL Trigger After Insert, Action JOIN 2 tables
body: <p>I need to create a trigger (after insert on one table) on MySQL, but the action needs to join 2 tables, for inserting into a third table. My script below returns no error, but the row is not inserted into the third table.</p>
  
  <p>The first table (on which the after-insert trigger should work):</p>
  
  <pre><code>Z_TAXO      
  ID  term_ID taxo_name
  1    1      dept
  2    2      staff
  3    4      course
  4    5      dept
  </code></pre>
  
  <p>The second table to be joined in the trigger:</p>
  
  <pre><code>Z_TERM  
  term_ID name
  1       Engineering
  2       Andy
  4       Metallurgy
  5       Business
  6       Arts
  </code></pre>
  
  <p>The third table. If the <code>Z_TAXO</code> table has a new row with <code>taxo_name = "dept"</code>, the row (joined with table <code>Z_TERM</code>) needs to be inserted into this table:</p>
  
  <pre><code>Z_DEPTS 
  ID  dept_name
  1   Engineering
  4   Business
  </code></pre>
  
  <p>I created a trigger:</p>
  
  <pre><code>delimiter //
      CREATE TRIGGER TRG_NEW_DEPT
      AFTER INSERT ON Z_TAXO
      FOR EACH ROW
      BEGIN
          DECLARE _dept_ID bigint(20);
          DECLARE _dept_name varchar(200);
          IF Z_TAXO.taxo_name = "DEPT" THEN
              BEGIN
                  SELECT Z_TAXO.ID INTO _dept_ID FROM Z_TAXO, Z_TERM
                  WHERE Z_TAXO.ID = new.Z_TAXO.ID AND Z_TAXO.term_ID = Z_TERM.term_ID;
                  SELECT Z_TERM.name INTO _dept_name FROM Z_TERM, Z_TAXO
                  WHERE Z_TAXO.term_ID = Z_TERM.term_ID AND Z_TAXO.ID = new.Z_TAXO.ID;
                  INSERT INTO Z_DEPTS (ID, dept_name) VALUES (_dept_ID, _dept_name);
              END;
          END IF;
      END//
  delimiter ;
  </code></pre>
  
  <p>Then inserted a row to the <code>Z_TAXO</code> table:</p>
  
  <pre><code>INSERT INTO Z_TAXO (ID, term_ID, taxo_name) VALUES (5, 6, "dept");
  </code></pre>
  
  <p>Expecting to have this new row in table <code>Z_DEPTS</code>:</p>
  
  <pre><code>ID  dept_name
  5   Arts
  </code></pre>
  
  <p>But when I <code>select * from Z_DEPTS</code>, the result is still: </p>
  
  <pre><code>ID  dept_name
  1   Engineering
  4   Business
  </code></pre>
  
  <p>What can be wrong? I can't modify the design of the tables, because they came from a wordpress Plugin. Thanks in advance!</p>