CREATE TRIGGER tg_tournaments_teams_st_goals BEFORE INSERT ON match_data
FOR EACH ROW BEGIN
    DECLARE teamIdofplayer INTEGER;
    DECLARE teamIdAwayMatch INTEGER;
    DECLARE teamIdHomeMatch INTEGER;
    DECLARE tournamentId INTEGER;

    CASE NEW.data_type  WHEN 'goal' THEN
          SELECT team_id INTO teamIdofplayer FROM players WHERE id = NEW.player_id;
          SELECT away_team_id,home_team_id,tournament_id INTO teamIdAwayMatch, teamIdHomeMatch, tournamentId FROM matches WHERE id = NEW.match_id;

          IF teamIdofplayer = teamIdHomeMatch THEN
            UPDATE tournaments_teams_st SET gf = gf + NEW.value, gf_local = gf_local + NEW.value WHERE tournament_id = tournamentId AND team_id = teamIdHomeMatch;
            UPDATE tournaments_teams_st SET ga = ga + NEW.value, ga_away = ga_away + NEW.value WHERE tournament_id = tournamentId AND team_id = teamIdAwayMatch;
          ELSE
            UPDATE tournaments_teams_st SET gf = gf + NEW.value, gf_away = gf_away + NEW.value WHERE tournament_id = tournamentId AND team_id = teamIdAwayMatch;
            UPDATE tournaments_teams_st SET ga = ga + NEW.value, ga_local = ga_local + NEW.value WHERE tournament_id = tournamentId AND team_id = teamIdHomeMatch;
          END IF//

          UPDATE tournaments_teams_st SET gd = gf - ga, gd_local = gf_local - ga_local, gd_away = gf_away - ga_away WHERE tournament_id = tournamentId AND (teamd_id = teamIdAwayMatch OR team_id = teamIdHomeMatch);

    END//
END//