Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Why the join in the second Insert? How many rows do you want to insert into doku_usergroup? –  ypercube Mar 21 '13 at 16:09
    
I just want to insert one row into doku_usergroup for each updated record of users. –  Luis Mar 21 '13 at 16:13

1 Answer 1

up vote 3 down vote accepted

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 ;
share|improve this answer
    
Thanks very much, I guess I wouldn't get at this solution by myself. –  Luis Mar 21 '13 at 23:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.