How to use triggers to update rows of other table?

Question

users:
+----+----------+-------------------------------------------+-------------------+-------+
| id | username | password                                  | email             | score |
+----+----------+-------------------------------------------+-------------------+-------+
|  1 | user_1   | *79457HG5456756799547645767567E0C18660CF8 | user1@example.com |     0 |
+----+----------+-------------------------------------------+-------------------+-------+
|  2 | user_2   | *79457HG5456756799547645767567E0C18660CF8 | user2@example.com |     0 |
+----+----------+-------------------------------------------+-------------------+-------+
|  3 | user_3   | *79457HG5456756799547645767567E0C18660CF8 | user3@example.com |     0 |
+----+----------+-------------------------------------------+-------------------+-------+

score_changes
+---------+-------+-------------+
| user_id | score | reason      |
+---------+-------+-------------+
|       1 |     2 | played well |
+---------+-------+-------------+
|       1 |    -2 | foul        |
+---------+-------+-------------+
|       2 |    -5 | spammed     |
+---------+-------+-------------+
|       3 |   -10 | cheated     |
+---------+-------+-------------+
|       1 |     1 | played well |
+---------+-------+-------------+

In the above setup, I want to calculate the sum of the score of each user in score_changes and update it to users.score. score_changes is dynamic and has inserts whenever a user gains score.

1. How do I update users.score automatically as and when a row is inserted in score_changes ? Is trigger the best option? How to implement it?
2. Is it better to recalculate the score by using SUM(SELECT score from score_changes where user_id = users.id); or just add the new change in users.score?

Answer 1

check this link you have use inner join.http://dev.mysql.com/doc/refman/5.5/en/update.html You have to join both the table first. Your question is same as this refer please: MySql update two tables at once