当表更新时，是否可以在SQL中自动更新表？

Question

I have three tables:

Table Results:

• id
• homeScore
• awayScore

Table Ladder:

• id
• points

Table Predictions

• id
• homeScore
• awayScore

I want to add points to all ids in Table Ladder, triggered by insertions, modifications or removals in Table Results, depending on the value of homeScore,awayScore from table Predictions.

And I have rules like:

• If user result and real result are identical, +5 points
• If user predicted correct winner, +2 points
• If user predicted correct goal difference +3 points
• If user predicted correct goals scored, +1 point (can happen for both teams)

Is this possible with only SQL? If not, how do I approach this with PHP?

Answer 1

You can do this if you define triggers. Compare new result with old result upon insert/update/delete and your problem will be solved. Read some tutorials about triggers for the RDBMS you are using and you will be able to solve the problem.