MySQL Forums
Forum List  »  Triggers

Cursor in trigger
Posted by: Andrew Roberts
Date: June 18, 2011 04:08AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Cursor in trigger
12013
June 18, 2011 04:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.