mysql - 创建MySQL触发器更新另一个表中的多条记录

Question

我不确定这是否可能。我正在开发一个梦幻高尔夫锦标赛应用程序作为一个项目。用户从 6 个组中挑选 6 个高尔夫球手，每个组包含 10 个高尔夫球手。高尔夫球手所在的组由高尔夫球 watch 中的组 bool 值确定。 scores表用于记录参赛作品。

我有两张 table 。高尔夫球 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)
            );

还有一个分数表。如下所示。

            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) 
            );

是否可以根据 golfscoretotal1 列之前的 golferid1 INT(6) 列中的高尔夫球手 ID 为每个高尔夫球手更新分数表(取自 golfers 表)中的分数。

Answer 1

你可以为 golferid1 使用像这样简单的东西:

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

然后为其他人创建更多这样的触发器，总共有 6 个触发器:

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