MySQL触发器不起作用，没有错误

Question

因此，我创建了一个触发器来更新插入时的另一个表，没有发现错误并且触发器存在(在 SHOW TRIGGERS 中列出，但在我插入后没有更新到另一个表一个。

触发器:

CREATE TRIGGER setmapsquare AFTER INSERT ON users

FOR EACH ROW
UPDATE map SET player_id = NEW.player_id WHERE map_id = NEW.startsquare;

它不会抛出任何错误，所以我现在不知道如何继续:/

编辑:插入也发生在这里:

$q = $DBC -> prepare( "
    INSERT INTO 
        users 
    ( username, password, email, startsquare ) 
    VALUES ( ?, ?, ?, (SELECT map_id FROM map WHERE player_id IS NULL ORDER BY RAND() LIMIT 1) );" );

插入的行1, 'Testjuuser', '2bd7fc520207eabc4026989728def05b541a863e', 'test@test', 38, 2014-08-31 09:20:28, 0, 0

并且map_id 38的player_id仍然为NULL

Answer 1

无法重现问题所描述的行为。 INSERT 语句应该失败并显示错误消息:

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

重现此问题的步骤:

CREATE TABLE users (
    player_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20),
    password VARCHAR(80),
    email VARCHAR(260),
    startsquare INT
);

CREATE TABLE map (
    map_id INT,
    player_id INT
);

为表格映射插入一些值

INSERT INTO map (map_id, player_id) VALUES 
(37, NULL),
(38, NULL),
(39, NULL);

创建触发器

DELIMITER //
CREATE TRIGGER setmapsquare AFTER INSERT ON users
FOR EACH ROW
UPDATE map SET player_id = NEW.player_id WHERE map_id = NEW.startsquare;
//
DELIMITER ;

试试这个插入

INSERT INTO users (username, password, email, startsquare) VALUES
(
    'Testjuuser', 
    '2bd7fc520207eabc4026989728def05b541a863e', 
    'test@test', 
    (SELECT map_id FROM map WHERE player_id IS NULL ORDER BY RAND() LIMIT 1)
);

并得到上面显示的错误消息，see this demo

注意:您必须注释掉 38 并删除选择之前的破折号。

虽然硬编码的 38 将按预期进行处理:

INSERT INTO users (username, password, email, startsquare) VALUES
(
    'Testjuuser', 
    '2bd7fc520207eabc4026989728def05b541a863e', 
    'test@test', 
    38
);

结论

实际的触发器语句不是问题中显示的语句或者存在错误，但 INSERT 应该完全失败。要检查触发器语句，可以对 INFORMATION_SCHEMA 使用以下查询:

SELECT 
    trigger_schema,         -- the database,
    event_object_table,     -- table
    action_timing,          -- should be 'after'
    event_manipulation,     -- should be 'INSERT'
    action_statement        -- the actual code
FROM 
    INFORMATION_SCHEMA.TRIGGERS 
WHERE 
    trigger_name = 'setmapsquare';

action_statement的内容是我的例子

UPDATE map SET player_id = NEW.player_id WHERE map_id = NEW.startsquare