Hi,

I am having difficulty using triggers in MySQL Workbench. I have defined the following code under the triggers tab (see below). I thought that I could use a cursor to walk over the source table that I created using SELECT, which joins two tables, however the code below updates the wrong row of the target table (matchRecord), and updates it using a summation applied to the entire source table. To make it clearer, the result of the SELECT action is:

matches, teams, goalsConceded
1, 1, 1
1, 2, 1
2, 1, 1
2, 2, 1 (goal inserted)

Inserting a goal into match 2 should update the matchRecord to produce:

match, homeTeam, homeTeamScore, awayTeam, awayTeamScore
1, 1, 1, 2, 1
2, 1, 1, 2, 1

Instead I get this:

match, homeTeam, homeTeamScore, awayTeam, awayTeamScore
1, 1, 1, 2, 4
2, 1, 0, 2, 1

In otherwords it takes only the first row from the source table, then repeatedly adds this single row to first row in the target table, when I would expect it to update the row in the target table corresponding to the inserted row in the source table.

I am a struggling a bit and I've tried every forum I can find.

Thanks for any help,

Andy.

-- Trigger DDL Statements
DELIMITER $$
CREATE TRIGGER project.trigger AFTER INSERT ON goalsRecord

FOR EACH ROW

BEGIN

DECLARE done INT DEFAULT 0;
DECLARE a, matches INT;
DECLARE b, teams INT;
DECLARE c, goalsConceded INT;

DECLARE cursor1 CURSOR FOR
SELECT matchRecord_matchID AS matches, teamRecord_teamID AS teams, COUNT(minutesOfGoal) AS goalsConceded
FROM goalsRecord LEFT JOIN matchTeamPlayerSelection
ON matchTeamPlayerSelection_playerRecord_concedingPla yerID = playerRecord_playerID
WHERE matchRecord_matchID = matchTeamPlayerSelection_matchRecord_matchID
GROUP BY teams, matches
ORDER BY matches, teams;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

UPDATE matchRecord
SET matchRecord.hometeamScore = DEFAULT
WHERE matchID != NULL;

UPDATE matchRecord
SET matchRecord.awayTeamScore = DEFAULT
WHERE matchID != NULL;

OPEN cursor1;

read_loop: LOOP

FETCH cursor1 INTO a,b,c;

IF done THEN
LEAVE read_loop;
END IF;

UPDATE matchRecord
SET matchRecord.homeTeamScore = c
WHERE matchRecord.matchID = a AND matchRecord.teamRecord_awayTeamID = b;

UPDATE matchRecord
SET matchRecord.awayTeamScore = c
WHERE matchRecord.matchID = a AND matchRecord.teamRecord_homeTeamID = b;

END LOOP;

CLOSE cursor1;

END;

$$

I have looked at this and I think the issue might be with your left join in the cursor definition. Do you really need this? It is not clear from your explanation above why this should be in there. Also when comparing again NULL values you must use matchID IS NULL or matchID IS NOT NULL. In your code above both will not match anything and nothing will get updated.