mysql - MySQL触发器出了什么问题

Question

CREATE TRIGGER updateRank BEFORE UPDATE ON question
SELECT SUM(favouriteCount) as fav FROM question WHERE created_user = NEW.created_user
FOR EACH ROW
    BEGIN
        UPDATE users SET rank = (SELECT id FROM Rank WHERE fav > range) WHERE id=NEW.created_user
    END

you have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT SUM(favouriteCount) as fav FROM question WHERE created_user = NEW.created' at line 2

如何解决？

Question table image

Rank table image

Answer 1

您不能使用类似触发器的聚合函数。但您可以将逻辑移至 UPDATE 中:

CREATE TRIGGER updateRank BEFORE UPDATE ON question
FOR EACH ROW
    BEGIN
        UPDATE users u CROSS JOIN
               (SELECT SUM(favouriteCount) as fav
                FROM question
                WHERE created_user = NEW.created_user
               ) f
            SET rank = (SELECT r.id FROM Rank r WHERE f.fav > r.range)
            WHERE u.id = NEW.created_user;
    END;