Android SQLite, how to use CASE statement in UPDATE?

Question

I have a student table:

CREATE TABLE students (name TEXT, grade INTEGER, level TEXT);

and I use this code to insert an entry:

INSERT INTO students(name, grade) VALUES('Allen', 80);

this works well except the level value is NULL, so I add a trigger on this table, this trigger will update the level depend on grade.

CREATE TRIGGER student_insert_trigger
AFTER INSERT ON students
BEGIN
    CASE
        WHEN NEW.gradle > 80 THEN 
            UPDATE students SET level = 'A' WHERE name = NEW.name;
        WHEN NEW.gradle < 60 THEN 
            UPDATE students SET level = 'C' WHERE name = NEW.name;
        ELSE 
            UPDATE students SET level = 'B' WHERE name = NEW.name;
    END
END

But it seems this sql code is error: near "CASE": syntax error, I have tried IF statement and got error ethier.

So how can such a function trigger be implemented?

---

I got an answer about modifying the SQL statement, It works fine in this case. But this question is a simple example based on my work, and in my work the problem is much more complex, that can not be solved by modifying the SQL statement.

What I need to do is kind of like an if else statement in a programming language, I found a lot of information on the Internet, but all the examples are using CASE THEN in SELECT or UPDATE.

I do need to use UPDATE or DELETE in CASE THEN ELSE(after THEN), but I'm not sure SQL allows that or not. Let's take a simple example, how to implments this SQL:

CREATE TRIGGER student_insert_trigger
AFTER INSERT ON students
BEGIN
    CASE
        WHEN NEW.gradle > 80 THEN 
            UPDATE students SET level = 'A' WHERE name = NEW.name;
        WHEN NEW.gradle < 60 THEN 
            DELETE FROM students WHERE name = NEW.name;
        ELSE 
            UPDATE students SET grade = 70 WHERE name = NEW.name;
    END
END

Answer 1

You should rewrite your code with this structure, Instead of the CASE statement, you should use the WHEN clause within the BEGIN ... END block. Here's how you can modify your trigger:

CREATE TRIGGER student_insert_trigger
AFTER INSERT ON students
FOR EACH ROW -- Note the addition of FOR EACH ROW
BEGIN
    UPDATE students
    SET level =
        CASE
            WHEN NEW.grade > 80 THEN 'A'
            WHEN NEW.grade < 60 THEN 'C'
            ELSE 'B'
        END
    WHERE name = NEW.name;
END;