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?