title: How can I create a trigger in MySql to automatically update a column based on another table?
body: <h2>EXAMPLE</h2>
  
  <p>Table 1 has 2 fields: (1) Company's Code &amp; (2) Supplier's Code
  Table 2 also has 2 fields: (1) Company's Code &amp; (2) Supplier's Code</p>
  
  <p>Table 1 is a 'directory' which matches the supplier's code to our company's code respectively. 
  For table 2, the supplier's code will be input by a person. The trigger will then search table 1 and find the matching company's code and automatically fill up the column.</p>
  
  <p>I created a trigger, but I realized my table became locked and I cannot add any data. An error appears: <strong>Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger.</strong></p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` TRIGGER `mydb`.`whole_rolls_AFTER_INSERT` AFTER INSERT ON `whole_rolls` FOR EACH ROW
  BEGIN
  UPDATE whole_rolls
  INNER JOIN reference_table on whole_rolls.supplierFoilCode = reference_table.supplierFoilCode
  SET whole_rolls.parentFoilCode = reference_table.parentFoilCode;
  END
  </code></pre>
  
  <p>So how can I achieve what i'm trying to do?</p>
  
  <hr>
  
  <p>SAMPLE DATA</p>
  
  <p>Table 1 (reference_table)</p>
  
  <pre><code> Parent Foil Code  Supplier Foil Code
      SLV1-AEEGLB            12345
      SLV2-AEEGLB            12346
      SLV3-AEEGLB            12347
      SLV4-AEEGLB            12348
      SLV5-AEEGLB            12349
  </code></pre>
  
  <p>Table 2 (whole_rolls)</p>
  
  <pre><code>Parent Foil Code  Supplier Foil Code
    [Automatic]           12348
  </code></pre>