Mysql – Two insert statements inside trigger – Duplicate entry ‘521-2’ for key ‘PRIMARY’

MySQLtrigger

I'm trying to create an insert trigger so that when a new user is inserted into users table (via site, httpd and php), part of this information is inserted in another table that manages access to a wiki.

User id of the new wiki user table register and default group id (2) should also be inserted in another wiki table, so that a new user belongs to a default group.

I coded this:

USE users_web;

DELIMITER |
CREATE TRIGGER doku_users_ins AFTER INSERT ON users_web.users
       FOR EACH ROW
       BEGIN
                INSERT INTO users_web.doku_users (login, pass, email)
                       VALUES (NEW.user, md5(NEW.pass), NEW.email);

                INSERT INTO users_web.doku_usergroup (uid, gid)
                       SELECT du.uid, "2" FROM doku_users du
                            INNER JOIN users_web.users u ON NEW.user = du.login;

       END |

DELIMITER ;

If the trigger is composed only of the first INSERT statement it works well, as the doku_users table is inserted of one more register.

But when I create the trigger as above, with the two INSERT statements, I receive an error as feedback: Duplicate entry '521-2' for key 'PRIMARY'.

I guess that the second sql statement tries to read the NEW value before it is available somehow.

How can I solve this issue?

Best Answer

I have been baffled by a similar situation. Use variables to store the values of NEW.column and remove the join from the 2nd Insert:

USE users_web;

DELIMITER |
CREATE TRIGGER doku_users_ins AFTER INSERT ON users_web.users
       FOR EACH ROW
       BEGIN

                DECLARE new_pass VARCHAR(255);         -- choose the datatypes
                DECLARE new_email VARCHAR(255);        -- 
                DECLARE new_user INT;                  --

                SET new_pass = NEW.pass ; 
                SET new_email = NEW.email ;
                SET new_user = NEW.user ;

                INSERT INTO users_web.doku_users (login, pass, email)
                       VALUES (new_user, md5(new_pass), new_email);

                INSERT INTO users_web.doku_usergroup (uid, gid)
                       SELECT du.uid, '2' FROM doku_users du
                            WHERE new_user = du.login;

       END |

DELIMITER ;
Related Question