Trying to create MySQL trigger with phpMyAdmin but getting MySQL error #1422

Question

I am trying to create a trigger in MySQL that is fired after a table gets updated. (I have had a little problem, that there will be a bunch of rows inserted before my code should be called. The first if statement is checking on that.) So I get the error #1422 - Explicit or implicit commit is not allowed in stored function or trigger.

That's my code:

DELIMITER //

CREATE TRIGGER
    `Update_Player_Rankings`
AFTER INSERT ON
    `sg_playerstats`
FOR EACH ROW

BEGIN
    SET
        @test1 = 0,
        @test2 = 0;

    SELECT
        `gameno`
    INTO
        @test1
    FROM
        `sg_gamestats`
    ORDER BY
        `gameno` DESC
    LIMIT
        0,
        1;

    SELECT
        COUNT(*)
    INTO
        @test2
    FROM
        `sg_playerstats`
    WHERE
        `gameno` = @test1
    GROUP BY
        `gameno`
    LIMIT
        0,
        1;

    IF
        @test1 = @test2
    THEN

        SET
            @var1 = 1,
            @var2 = 0;

        SELECT
            `id`
        INTO
            @var1
        FROM
            `sg_playerstats`
        ORDER BY
            `id` DESC
        LIMIT
            0,
            1;

        SELECT
            `Last Updated`
        INTO
            @var2
        FROM
            `sg_playerranking`
        LIMIT
            0,
            1;

        IF
            @var1 > @var2
        THEN

            TRUNCATE
                `sg_playerranking`;

            SET
                @Rank := 0;

            INSERT INTO
                `sg_playerranking`
            SELECT
                @Rank := @Rank + 1 AS `Rank`,
                t.*,
                @var1 AS `Last Updated`
            FROM
                (
                    SELECT
                        `player` AS `Player`,
                        SUM(1) AS `Games`,
                        SUM(`points`) AS `Points`,
                        SUM(`points`) / SUM(1) AS `Points per Game`,
                        SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
                        SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
                        SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
                        SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
                        SUM(`kills`) AS `Kills`,
                        SUM(`kills`) / SUM(1) AS `Kills per Game`,
                        SUM(`death`) AS `Deaths`,
                        SUM(`death`) / SUM(1) AS `Deaths per Game`,
                        SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
                        ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
                    FROM
                        `sg_playerstats`
                    GROUP BY
                        `player`
                    ORDER BY
                        `Score` DESC,
                        `Points per Game` DESC,
                        `Wins per Game` DESC,
                        `Loses per Game` ASC,
                        `Kills per Game` DESC,
                        `Deaths per Game` ASC,
                        `Points` DESC,
                        `Wins` DESC,
                        `Loses` ASC,
                        `Kills` DESC,
                        `Deaths` ASC,
                        `Games` DESC,
                        `Time played` DESC,
                        `Player` DESC
                ) t;
        END IF;
    END IF;
END;

//

DELIMITER;

I hope you can help!

spencer7593 · Accepted Answer · 2013-01-24 00:07:49Z

2

The problem is your TRUNCATE statement. That statement cannot be executed from a trigger, because that statement performs an implicit commit. (There may be other reasons as well, but that one is sufficient.)

answered Jan 24, 2013 at 0:07

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BrainStone Over a year ago

So how do i solve this? I have to clear the table in order to make everything work!

spencer7593 Over a year ago

Is there a reason you can't use a DELETE statement instead?

Collectives™ on Stack Overflow

Trying to create MySQL trigger with phpMyAdmin but getting MySQL error #1422

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related