mysql - 如何触发一个表并更新同一个表？

Question

我正在做一个触发器，当我更新表时，同一个表会更新一个字段。触发器是:

CREATE TRIGGER update_user
AFTER UPDATE
ON users
FOR EACH ROW
BEGIN
    IF (NEW.totalPoints > 0 AND NEW.totalPoints < 200)  
    THEN
        updateUser("rank 1", NEW.ID);  
    ELSEIF (NEW.totalPoints > 200 AND NEW.totalPoints < 400)  
    THEN
        updateUser("rank 2", NEW.ID);
    END IF;
END;

程序是:

CREATE PROCEDURE updateUser(newRank VARCHAR(100), IDUser INT)
BEGIN
  UPDATE users SET rank = newRank WHERE ID = IDUser;
END;

我在创建过程时遇到的错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

感谢您的帮助!!

Answer 1

让我们尝试使用分隔符

DELIMITER $$    
    CREATE TRIGGER update_user AFTER UPDATE ON users
    FOR EACH ROW
    BEGIN
        IF (NEW.totalPoints > 0 AND NEW.totalPoints < 200)  
        THEN
            SET NEW.rank = 'rank 1';
        ELSEIF (NEW.totalPoints > 200 AND NEW.totalPoints < 400)  
        THEN
            SET NEW.rank = 'rank 2';
        END IF;
    END$$
    DELIMITER ;