MySQL Trigger IF Statement Error (Won't Create)

Question

I want to create Trigger like this, if student have score above like this the score on other column will automatic inserting value (alphabetic)

 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 ;

it says there is error in line 26 it's on the

END IF;

can someone explain and provide some solution for me

i don't know it's say the eror in line 26 END IF; please help me . — RedHatSource
– RedHatSource, Commented Feb 10, 2015 at 19:54

peterm · Accepted Answer · 2015-02-15 21:26:31Z

You can't use UPDATE but you can set the value of NEW.nilai_hurufbased on NEW.nilai_angka because it's a BEFORE trigger.

Use CASE expression to make more succinct.

Since it's now a one statement trigger you no longer need to use 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;

Here is a SQLFiddle demo

You most likely want to create a complimentary BEFORE UPDATE trigger. At this point it makes sense to extract the logic into a function

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;

And then reuse it in both triggers

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);

Here is a SQLFiddle demo

Now, alternatively you can ditch triggers and nilai_huruf column altogether and just use the function whenever you fetch your records

SELECT nilai_angka, angka_huruf(nilai_angka) nilai_huruf
  FROM nilai;

Here is a SQLFiddle demo

Thanks peterm for the answer but i've already solved this problem i change event on the trigger to AFTER

RedHatSource · Accepted Answer · 2015-02-18 00:20:34Z

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

This is the Answer i change the event on the trigger

Collectives™ on Stack Overflow

MySQL Trigger IF Statement Error (Won't Create)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related