mysql - #1442 - 无法更新存储函数/触发器中的表 '*'，因为它已被调用此存储函数/触发器的语句使用

Question

我真的不擅长 SQL，但我正在努力使我的数据库尽可能简单和简约。不复制值，而是使用引用和 ID。

现在插入查询后我得到错误

1442 - Can't update table 'signed' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

查询可以在禁用触发器时插入。但是我的查询只是从表“用户”中读取，所以不应该有冲突。我阅读了一些关于死锁的内容——查询在使用它或类似的东西时锁定了表，因此触发器无法更新行？我如何才能实现这一目标？

我简化了我的查询，所以只有重要的东西在那里。

查询:

INSERT INTO signed (time_of_start, player)
    SELECT time_of_game_id, users.user_id 
    FROM time_of_game, users
    WHERE time_of_game.time_of_start = "2017-02-01 12:00:00"
      AND users.steamid = "1234567890123456";

触发器:

CREATE TRIGGER `payment_for_joining` 
AFTER INSERT ON `signed`
FOR EACH ROW 
    UPDATE users  
    SET users.credit = users.credit-1
    WHERE users.user_id = NEW.player

Answer 1

它不允许您更新表，因为它已经被调用此触发器的 INSERT INTO..SELECT 查询读取。

另一种方法是禁用触发器并单独更新用户表，例如:

INSERT INTO signed (time_of_start, player)
SELECT time_of_game_id, users.user_id FROM time_of_game, users
WHERE time_of_game.time_of_start="2017-02-01 12:00:00"
AND users.steamid="1234567890123456";

UPDATE users join time_of_game SET users.credit = users.credit-1
where time_of_game.time_of_start="2017-02-01 12:00:00"
AND users.steamid="1234567890123456";

如果有任何列链接这两个表，您可以添加一个带有 ON 子句的连接列。