Trying to update a table using a trigger in mysql

Question

When the rsform_submission_values table has an entry with certain criteria in two columns, I need to insert a row in a table called user_usergroup_map. To add a row to user_usergroup_map, I need to lookup the UserId in a table called ch54b_rsform_submission as the userID isn't stored in rsform_submission_values. The common column to join the two tables is SubmissionId.

I have been looking at a trigger to achieve this, however, it doesn't write anything to the user_usergroup_map table.

Is there something obviously wrong with the mysql below? Or is there a way to troubleshoot a trigger in MySQL so I can see if there are any issues?

Thank you Pete.

IF NEW.FieldName = '_STATUS' AND NEW.FieldValue = '1' THEN
        INSERT INTO ch54b_user_usergroup_map (`user_id`, `group_id`) 
        SELECT UserId, '12' 
        FROM ch54b_rsform_submission_values 
        INNER JOIN ch54b_rsform_submission ON ch54b_rsform_submission_values.SubmissionId = ch54b_rsform_submissions.SubmissionId;
    END IF

I have been looking at a trigger to achieve this, however, it doesn't write anything into the user_usergroup_map table.

Answer 1

Your query seems to be attempting to insert all users into user_usergroup_map, rather than the one related to the row being inserted/updated. Therefore I suggest something simpler:

IF NEW.FieldName = '_STATUS' AND NEW.FieldValue = '1' THEN
    INSERT INTO ch54b_user_usergroup_map (`user_id`, `group_id`) 
    SELECT UserId, 12
    FROM ch54b_rsform_submission
    WHERE SubmissionId = NEW.SubmissionId;
END IF

NB if you have a unique key on user_usergroup_map (user_id, group_id) and you want to handle the case where the user is already in that group, then you could change INSERT to INSERT IGNORE to avoid an error