title: MySQL: Update column with TRIGGER BEFORE INSERT
body: <p>I have this code that prevents duplicates if column C is equal to a specific value</p>
  
  <pre><code>DELIMITER $$
  
  CREATE TRIGGER special_unique_test BEFORE INSERT ON myTable 
      FOR EACH ROW BEGIN
         IF NEW.C = 'x' AND EXISTS (
            SELECT * FROM myTable WHERE A = NEW.A AND B = NEW.B AND C = 'x'
         )THEN
  
            UPDATE myTable SET D = D + 1 WHERE A = NEW.A AND B = NEW.B AND C = 'x';
  
         END IF;
      END$$
  
  DELIMITER ;
  </code></pre>
  
  <p>Everything works except that the update statement isn't executed.</p>
  
  <p><strong>ADDED</strong></p>
  
  <p>Tried this in PhpMyAdmin and I got this error:</p>
  
  <p>1442 - Can't update table 'myTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.</p>
  
  <p>I could go around this by making a separate table for the D column but it would be nice to have it in the same table.</p>
  
  <p>Extra question: How can I make this error show when I insert something from my website using mysqli?</p>