Update value in table based on condition in other table with trigger I have 2 tables, first stores the details of all the races 1:  and second the users who have joined which races 2:

I want to update the race_status to Full and record_status to 2 in race table, if race_id count in second table is 5 per race_id. I have created a MySQL trigger for it here is the trigger code for it: 

CREATE TRIGGER `user_race_count` BEFORE INSERT ON `tbl_map_race`
    FOR EACH ROW BEGIN
    DECLARE a1,a2 INT;
    SELECT tbl_map_race.race_id, COUNT(tbl_map_race.race_id) INTO a1,a2
    FROM `tbl_map_race`
    GROUP BY tbl_map_race.race_id;
    IF ((a1 = NEW.race_id) AND (a2&gt;=5))
    THEN 
        UPDATE tbl_race 
        SET tbl_race.race_status = 'Full', tbl_race.record_status = '2' 
        WHERE tbl_race.race_id = @r_id; 
    END IF; 
END


In the trigger tbl_race is the first table and tbl_map_race is the second table.
Trigger is successfully getting created but when I run query to join the race with trigger working behind I'm not able to update values in first table, but if I drop the trigger and run the query I'm able to insert a record in second table. Any help to this would be appreciated. Thank You.
CREATE TRIGGER `user_race_count` 
AFTER INSERT 
ON `tbl_map_race`
FOR EACH ROW 
BEGIN
    IF 5 &lt;= ( SELECT COUNT( /* DISTINCT */ user_id) 
              FROM tbl_map_race
              WHERE race_id = NEW.race_id)
    THEN 
        UPDATE tbl_race 
        SET race_status = 'Full'
          , record_status = '2' 
        WHERE race_id = NEW.race_id; 
    END IF; 
END