mysql - 触发器不允许我更新

Question

我有以下触发器:

  delimiter |
CREATE TRIGGER DES1actualizarConsultaR_update
AFTER UPDATE ON ranking
FOR EACH ROW
BEGIN
UPDATE player P,
    (SELECT 
        J.player_id AS jugador_id,
            SUM(ranking_points) AS PuntosTotales
    FROM
        player J
    INNER JOIN ranking R ON R.player_id = NEW.player_id
    GROUP BY J.player_id) AS TD 
SET 
    P.totalPuntos = TD.PuntosTotales
WHERE
    P.player_id = TD.jugador_id;

UPDATE player P,
    (SELECT 
        J.player_id AS JnumUno, COUNT(*) AS NumVeces1
    FROM
        player J
    INNER JOIN ranking R ON R.player_id = NEW.player_id
    WHERE
        R.ranking = '1'
    GROUP BY J.player_id) AS TD 
SET 
    P.vecesNum1 = TD.NumVeces1
WHERE
    P.player_id = TD.JnumUno;

END |

我正在尝试执行此更新:

UPDATE ranking 
SET 
    ranking_points = (ranking_points + 10)
WHERE
    player_id IN (SELECT 
            P.player_id
        FROM
            player P,
            tourney T,
            tmatch M
        WHERE
            T.tourney_name LIKE 'Madrid%'
                AND T.tourney_date BETWEEN '2017-01-01' AND '2018-12-31'
                AND (M.round = 'F' OR M.round = 'SF')
                AND P.player_id = M.winner_id
                AND M.tourney_id = T.tourney_id);

但是我有这个错误:

Error Code: 1442. Can't update table 'player' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

如何进行更新查询？

我正在使用 MYSQL。

Answer 1

发生这种情况是因为您在 TRIGGER 的 UPDATE 语句中的 ranking 表上存在关联。

即使您没有更新ranking表本身，MySQL 也不喜欢它。

您必须首先执行独立的 SELECT 查询来计算玩家的分数，将这些分数保存在变量中，然后在 player 表的 UPDATE 中使用它来解决这个问题，不引用排名表

这个想法是这样的:

 delimiter |
CREATE TRIGGER DES1actualizarConsultaR_update
AFTER UPDATE ON ranking
FOR EACH ROW
BEGIN

    DECLARE vPuntosTotales INT;
    DECLARE vNumVeces1 INT;

    SELECT SUM(ranking_points) AS PuntosTotales INTO vPuntosTotales 
    FROM ranking  
    WHERE player_id = NEW.player_id;


    UPDATE player SET totalPuntos = vPuntosTotales WHERE player_id = NEW.player_id;


    -- NOT SURE IF THIS ONE IS CORRECT
    SELECT COUNT(*) AS NumVeces1 INTO vNumVeces1 
    FROM ranking R 
    WHERE R.ranking = '1' AND R.player_id = NEW.player_id;  


    UPDATE player SET vecesNum1 = vNumVeces1 WHERE player_id = NEW.player_id;

END |

您应该检查我的查询，因为我不太清楚您的表结构，而且我的 SELECT 可能是错误的，尤其是第二个