mysqltriggersonupdate

Create MySQL Trigger to update multiple records in another table


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.


Solution

  • 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