title: adding MySQL column to a table using trigger
body: <p>I have a table that looks like this:</p>
  
  <p><strong>Counters:</strong></p>
  
  <p>| <em>client_id</em>  | <em>provider1</em>   |   <em>provider2</em>   |   <em>provider3</em> |</p>
  
  <p>_ - _ - _- _ - _ - _- _ - _ - _</p>
  
  <p>the column names <code>[provider1,provider2,...]</code> is the id of provider which is stored in another table called "providers".</p>
  
  <p>what i need is, when a new provider is inserted into the "proviers" database a trigger would automatically add another column in the "Counters" table, but I can't seem to be able to get it.</p>
  
  <hr>
  
  <p>this is what i have so far:</p>
  
  <pre><code>CREATE TRIGGER `providers_AFTER_INSERT` AFTER INSERT ON `providers` FOR EACH ROW
      ALTER TABLE `counters` ADD NEW.prov_id int;
  </code></pre>
  
  <p>the error is at the '.' between 'NEW; and 'prov_id' with the error:</p>
  
  <pre><code>Syntax error: unexpected '.' (dot)
  </code></pre>
  
  <p>I am guessing it needs a string value as the name of the column so i searched around and found a way to pass it as a string:</p>
  
  <pre><code>CREATE TRIGGER `providers_AFTER_INSERT` AFTER INSERT ON `providers` FOR EACH ROW
      ALTER TABLE `counters` ADD CAST(NEW.prov_id AS CHAR CHARACTER SET utf8) int;
  </code></pre>
  
  <p>but this shows me an error at the 'ADD' saying it is unexpected.</p>
  
  <p>also tried surrounding the variable with brackets <code>{NEW.prov_id}</code> but to no avail, it gives the same error at the 'ADD' clause.</p>
  
  <p>also, while searching I also in some posts that says it does not work while some say it does work, and those who say it works don't use a variable for the column name.</p>
  
  <p>how can i fix this error? and is it even doable?</p>
  
  <p>my MySQL is v5.0
  I can upgrade it, if necessary.</p>
  
  <p>Thank you in advnace</p>