Create MySQL Trigger to update multiple records in another table

Question

Im not to sure if this is possible. I'm developing a fantasy golf tournament App asr a project. The user picks 6 golfers from six groups, each group contains ten golfers. The group that the golfer is in is determined by the group boolean in the golfers table. The scores table is used to record the competition entries.

I have two tables. A golfers table.

            CREATE TABLE golfers (
            golferid INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            firstname VARCHAR(30) NOT NULL,
            secondtname VARCHAR(30) NOT NULL,
            country VARCHAR(50),
            worldranking int(3),
            tournamentposition int(2),
            group1 boolean,
            group2 boolean,
            group3 boolean,
            group4 boolean,
            group5 boolean,
            group6 boolean,
            day1score int(2),
            day2score int(2),
            day3score int(2),
            day4score int(2),
            totalscore int(3),
            golfscoretotal int(3)
            );

And a scores table. As seen below.

            CREATE TABLE scores (
            scoreid INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            userid INT(11) NOT NULL,
            golferid1 INT(6),
            golfscoretotal1 INT(3),
            golferid2 INT(6),
            golfscoretotal2 INT(3),
            golferid3 INT(6),
            golfscoretotal3 INT(3),
            golferid4 INT(6),
            golfscoretotal4 INT(3),
            golferid5 INT(6),
            golfscoretotal5 INT(3),
            golferid6 INT(6),
            golfscoretotal6 INT(3),
            totalscore INT(4),
            FOREIGN KEY fk_userid REFERENCES users(id) 
            );

Is it possible to update the scores in the scores table (taken from the golfers table) for each golfer based on the id of the golfer in the golferid1 INT(6) column before the golfscoretotal1 column.

it is possible, but Why would you want this table? It doesn't seem like a good plan at the moment. The only possible reason you would want a table scores like this is if you added a date along with it. Otherwise you can always query your golfers table to display the results in any way you want. — davejal, Jan 06 '16 at 11:26
On second thought even with the day it would be a bad idea, because have you considered that golfers id's could be deleted and other id created resulting in different id's or more then 6 golfers, what would happen then to the table scores? — davejal, Jan 06 '16 at 11:29
Golfers cant be deleted Dave. The competition users must pick ten golfers from 60 for a given tournament. Im using the the scores table to record the competition scores — David Mac, Jan 06 '16 at 11:46
Maybe I haven't explained this entirely. Its a fantasy golf tournament App im creating for a project. The user picks 6 golfers from six groups each group contains ten golfers. The group that the golfer is in is determined by the group booleans in the golfers table. The scores table is used to record the competition entries — David Mac, Jan 06 '16 at 11:51
This gives more meaning to it yes. Thanks for the explanation. — davejal, Jan 06 '16 at 11:58
apologies Dave, Ive revised the question at the top as well. — David Mac, Jan 06 '16 at 11:59

score -1 · Accepted Answer · answered Jan 06 '16 at 12:13

-1

You could use something simple like this for golferid1:

CREATE TRIGGER update_scores1 After INSERT ON golfers FOR EACH ROW 
       UPDATE scores
       SET golfscoretotal1 = new.golfscoretotal
       WHERE golferid1 = NEW.golferid

Then for the others just create more triggers like this, resulting in a total of 6 triggers:

CREATE TRIGGER update_scores2 After INSERT ON golfers FOR EACH ROW 
       UPDATE scores
       SET golfscoretotal2 = new.golfscoretotal
       WHERE golferid2 = NEW.golferid

answered Jan 06 '16 at 12:13

davejal

6,009
10
39
82

another option would be to use if-then statements, but I can't test this. You could see [this answer](http://stackoverflow.com/a/16892291/3664960) for reference – davejal Jan 06 '16 at 12:15
Thanks Dave, I'll test now and I'll get back to you shortly. I appreciate that. – David Mac Jan 06 '16 at 12:23
Dave, Thanks very much, This worked a treat, I really appreciate that. – David Mac Jan 06 '16 at 15:23
No Problem, glad it worked. You can eventually use a more expert way by using `if` (with the example provided in the link). – davejal Jan 06 '16 at 15:31

Create MySQL Trigger to update multiple records in another table

1 Answers1