Cursor in trigger

[Andrew Roberts]

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:

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

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_concedingPlayerID = 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;

$$



Edited 2 time(s). Last edit at 06/18/2011 04:31AM by Andrew Roberts.