Update value in table based on condition in other table with trigger
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