MySQL 触发器 IF 语句错误(不会创建)

Question

如果学生的分数高于这样，我想创建这样的触发器 其他列上的分数将自动插入值(按字母顺序)

 DELIMITER |
    CREATE TRIGGER N_Angka BEFORE INSERT ON nilai
    FOR EACH ROW
    BEGIN
        IF nilai_angka <= 50 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'C-';
            ELSE IF nilai_angka < 55 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'C';
            ELSE IF nilai_angka < 59 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'C+';
            ELSE IF nilai_angka <= 60 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'B-';
            ELSE IF nilai_angka < 75 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'B';
            ELSE IF nilai_angka <= 80 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'B+';
            ELSE IF nilai_angka <= 85 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'A-';
            ELSE IF nilai_angka <= 90 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'A';
            ELSE IF nilai_angka <= 99 THEN
                UPDATE nilai SET NEW.nilai_huruf = 'A+';
            ELSE
                UPDATE nilai SET NEW.nilai_huruf = 'S';
            END IF;
    END |
    DELIMITER ;

它说第 26 行有错误

END IF;

有人可以解释一下并为我提供一些解决方案吗

Answer 1

您不能使用UPDATE，但您可以根据NEW.nilai_angka设置NEW.nilai_huruf的值，因为它是一个之前触发。

使用CASE表达更加简洁。

由于它现在是单语句触发器，因此您不再需要使用 BEGIN...END block 。

CREATE TRIGGER N_Angka
BEFORE INSERT ON nilai
FOR EACH ROW
  SET NEW.nilai_huruf = CASE 
    WHEN NEW.nilai_angka <=50              THEN 'C-'
    WHEN NEW.nilai_angka BETWEEN 51 AND 54 THEN 'C'
    WHEN NEW.nilai_angka BETWEEN 55 AND 58 THEN 'C+'
    WHEN NEW.nilai_angka BETWEEN 59 AND 60 THEN 'B-'
    WHEN NEW.nilai_angka BETWEEN 61 AND 74 THEN 'B'
    WHEN NEW.nilai_angka BETWEEN 75 AND 80 THEN 'B+'
    WHEN NEW.nilai_angka BETWEEN 81 AND 85 THEN 'A-'
    WHEN NEW.nilai_angka BETWEEN 86 AND 90 THEN 'A'
    WHEN NEW.nilai_angka BETWEEN 91 AND 99 THEN 'A+'
    ELSE 'S' END;

这是一个 SQLFiddle 演示

<小时/>

您很可能想要创建一个免费的 BEFORE UPDATE 触发器。此时将逻辑提取到函数中是有意义的

CREATE FUNCTION angka_huruf(_angka INT)
RETURNS VARCHAR(2) DETERMINISTIC
RETURN CASE 
  WHEN _angka <=50              THEN 'C-'
  WHEN _angka BETWEEN 51 AND 54 THEN 'C'
  WHEN _angka BETWEEN 55 AND 58 THEN 'C+'
  WHEN _angka BETWEEN 59 AND 60 THEN 'B-'
  WHEN _angka BETWEEN 61 AND 74 THEN 'B'
  WHEN _angka BETWEEN 75 AND 80 THEN 'B+'
  WHEN _angka BETWEEN 81 AND 85 THEN 'A-'
  WHEN _angka BETWEEN 86 AND 90 THEN 'A'
  WHEN _angka BETWEEN 91 AND 99 THEN 'A+'
  ELSE 'S' END;

然后在两个触发器中重复使用它

CREATE TRIGGER N_Angka
BEFORE INSERT ON nilai
FOR EACH ROW
  SET NEW.nilai_huruf = angka_huruf(NEW.nilai_angka);

CREATE TRIGGER N_Angka2
BEFORE UPDATE ON nilai
FOR EACH ROW
  SET NEW.nilai_huruf = angka_huruf(NEW.nilai_angka);

这是一个 SQLFiddle 演示

<小时/>

现在，您也可以完全放弃触发器和 nilai_huruf 列，只在获取记录时使用该函数

SELECT nilai_angka, angka_huruf(nilai_angka) nilai_huruf
  FROM nilai;

这是一个 SQLFiddle 演示