CODEWITHSUNDEEP
mysqlsql
Shann
Shann

Reputation: 690

Mysql trigger after update update

I have a table as below:

+---------+------------+-------+-------+--------+--------+--------+
| Matchid | Date       | TeamA | TeamB | ScoreA | ScoreB | Winner |
+---------+------------+-------+-------+--------+--------+--------+
|       1 | 2014-06-12 | BRA   | CRO   |   null |   null | NULL   |

This is a fixture table for a soccer game.

After the match is played, I want to update the ScoreA and ScoreB, then with a trigger after update or even before update, I'll compare the scores and update the winner field accordingly.

When I create update trigger on the table, I am unable to update any of the fields, I read that this is not possible. Then I thought of creating a log of the update, i.e I create a similar table and I wanted to insert both all the new and old records in the log table. But this is not possible also.

Is there any way where I can accomplish this? Thanks

Upvotes: 0

Views: 171

Answers (1)

Barmar
Barmar

Reputation: 782785

You can update the fields in a BEFORE UPDATE trigger.

CREATE TRIGGER set_winner BEFORE UPDATE
ON ScoreTable
FOR EACH ROW
    SET NEW.Winner = CASE WHEN NEW.ScoreA IS NULL OR NEW.ScoreB IS NULL THEN NULL
                          WHEN NEW.ScoreA > NEW.ScoreB THEN NEW.TeamA
                          WHEN NEW.ScoreB > NEW.ScoreA THEN NEW.TeamB
                          ELSE "Tie"
                     END

DEMO

Upvotes: 3

Related Questions