Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Unanswered: Cursor in trigger

    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;

    $$

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •