MySQL trigger will not work, no error

Question

So I created a trigger to update another table on an insert, no errors found and the trigger exists(gets listed in SHOW TRIGGERS, but there is no update to the other table after I insert to one.

The trigger:

CREATE TRIGGER setmapsquare AFTER INSERT ON users

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

It throws no errors, so I do not now how to proceed :/

EDIT: also the insert takes place here:

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

The inserted row 1, 'Testjuuser', '2bd7fc520207eabc4026989728def05b541a863e', 'test@test', 38, 2014-08-31 09:20:28, 0, 0

and the map_id 38 still has NULL as its player_id

Answer 1

The behavior as described by the question can't be reproduced. The INSERT statement should fail with the error message:

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.

Steps to reproduce this:

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 some values for the table map

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

Create the trigger

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 ;

Try this insert

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

and get the error message shown above, see this demo

Note: You've got to comment out the 38 and remove the dashes before the select.

While the hard coded 38 will processed as expected:

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

Conclusion

Either the actual trigger statement is not that shown in the question or there are errors, but then the INSERT should fail completely. To check the trigger statement one could use following query against the 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';

The content of action_statement is in my case

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