title: MariaDB - complex trigger query after update
body: <p>I have three tables  ( <em>restaurants - votes - comments</em>) . I want to create  a trigger which let me update two fields in restaurants ( <em>restaurant_rate_average , restaurant_comment_count</em> ) . Here is my trigger :</p>
  
  <pre><code>DELIMITER $$
  
  CREATE
      TRIGGER `change comment status` AFTER UPDATE ON `comments` 
      FOR EACH ROW
      BEGIN
  
         UPDATE restaurants
         INNER JOIN votes ON restaurants.restaurant_id = votes.vote_foreign_key
         SET restaurants.restaurant_comment_count = SELECT (COUNT(votes.vote_id))
         WHERE votes.vote_model = 'restaurant'
         AND WHERE old.comment_status&lt;&gt;NEW.comment_status;
  
         UPDATE restaurants
         INNER JOIN votes ON restaurants.restaurant_id = votes.vote_foreign_key
         SET restaurants.restaurant_rate_average = SELECT (AVG(votes.vote_num))
         WHERE votes.vote_model = 'restaurant'
         AND WHERE old.comment_status&lt;&gt;NEW.comment_status;
      END;
  $$
  
  DELIMITER ;
  </code></pre>
  
  <p>Actually I have an error :</p>
  
  <pre><code>#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT (COUNT(votes.vote_id))
         WHERE votes.vote_model = 'restaurant'
        ' at line 8
  </code></pre>
  
  <p>I've tried to make complexion less and replace SELECT (COUNT(votes.vote_id)) with 2 and I get new error :</p>
  
  <pre><code>#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE comments.comment_status&lt;&gt;NEW.comment_status;
  
         UPDATE resta' at line 9
  </code></pre>
  
  <p>How to resolve it?</p>
  
  <p><strong>EDITED :</strong> </p>
  
  <p>here is my pseudo tables :</p>
  
  <p><strong>restaurants:</strong> 
  restaurant_id, restaurant_comment_count(int) , restaurant_rate_average(int)</p>
  
  <p><strong>comments:</strong>
  comment_id , comment_status(boolean) , comment_content(varchar) , votes_vote_id(id)</p>
  
  <p><strong>votes:</strong>
  vote_id , vote_foreign_key(id), vote_model(varchar)</p>
  
  <p><strong>EDITED 2 :</strong></p>
  
  <p>I create new simple trigger which just join three tables and it works like charm:</p>
  
  <pre><code>BEGIN
     UPDATE restaurants res
     INNER JOIN votes v ON v.vote_foreign_key = res.restaurant_id AND v.vote_foreign_key = 'restaurant'
     INNER JOIN comments cm ON cm.votes_vote_id = v.vote_id
     SET res.restaurant_rate_average = 2
     WHERE cm.comment_status = NEW.comment_status;
  END
  </code></pre>
  
  <p>But if I replace <em>SET res.restaurant_rate_average = 2</em> with  <em>SELECT AVG(v.vote_id)</em> I got an error. I also edit last trigger to :</p>
  
  <pre><code>BEGIN
     DECLARE RESTAURANT_RATE_AVERAGE INTEGER;
     SET RESTAURANT_RATE_AVERAGE := (SELECT AVG(votes.vote_num)
     UPDATE restaurants res
     INNER JOIN votes v ON v.vote_foreign_key = res.restaurant_id AND v.vote_foreign_key = 'restaurant'
     INNER JOIN comments cm ON cm.votes_vote_id = v.vote_id
     SET restaurant_rate_average = RESTAURANT_RATE_AVERAGE
     WHERE cm.comment_status = NEW.comment_status;
  END
  </code></pre>
  
  <p>I got an error in line 4. All I want is replace static number with <code>SELECT AVG(votes.vote_num)</code></p>