Sqlite trigger with 'case when' statement

Question

I want to get the last inserted ID of every channel from log table then write it to another table. For this purpose I wrote a trigger on log table, but it doesn't work because of a syntax error.

A syntax the used for case statement, exactly like a Sqlite reference.

CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END  
CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END 

My code:

CREATE TRIGGER ChnState_log AFTER INSERT 
ON CallLog
BEGIN
    CASE NEW.Dir
        WHEN 0
        BEGIN
            CASE
                WHEN 0=(SELECT Id FROM ChnStatus WHERE No = NEW.SrcNo)
                BEGIN
                    INSERT INTO Setting(Name, Value) VALUES ("LstChnSt0", NEW.ID);
                END;
                WHEN 1=(SELECT Id FROM ChnStatus WHERE No = NEW.SrcNo)
                BEGIN
                    INSERT INTO Setting(Name, Value) VALUES ("LstChnSt1", NEW.ID);
                END;
                WHEN 2=(SELECT Id FROM ChnStatus WHERE No = NEW.SrcNo)
                BEGIN
                    INSERT INTO Setting(Name, Value) VALUES ("LstChnSt2", NEW.ID);
                END;
                WHEN 3=(SELECT Id FROM ChnStatus WHERE No = NEW.SrcNo)
                BEGIN
                    INSERT INTO Setting(Name, Value) VALUES ("LstChnSt3", NEW.ID);
                END;
            END;
        END;

        WHEN 1
        BEGIN
            CASE
                WHEN 0=(SELECT Id FROM ChnStatus WHERE No = NEW.DestNo)
                BEGIN
                    INSERT INTO Setting(Name, Value) VALUES ("LstChnSt0", NEW.ID);
                END;
                WHEN 1=(SELECT Id FROM ChnStatus WHERE No = NEW.DestNo)
                BEGIN
                    INSERT INTO Setting(Name, Value) VALUES ("LstChnSt1", NEW.ID);
                END;
                WHEN 2=(SELECT Id FROM ChnStatus WHERE No = NEW.DestNo)
                BEGIN
                    INSERT INTO Setting(Name, Value) VALUES ("LstChnSt2", NEW.ID);
                END;
                WHEN 3=(SELECT Id FROM ChnStatus WHERE No = NEW.DestNo)
                BEGIN
                    INSERT INTO Setting(Name, Value) VALUES ("LstChnSt3", NEW.ID);
                END;
            END;
        END;
    END;
END;

Answer 1

A CASE expression can be used only to select between other expressions, not for statements like INSERT.

The WHEN clause of the CREATE TRIGGER statement often helps. For anything else, you have to put the logic inside the actual statements, like this:

CREATE TRIGGER ChnState_log_src
AFTER INSERT ON CallLog
FOR EACH ROW
WHEN NEW.Dir = 0
BEGIN
    INSERT INTO Setting(Name, Value)
    VALUES('LstChnSt' || (SELECT Id
                          FROM ChnStatus
                          WHERE No = NEW.SrcNo),
           NEW.ID);
END;

CREATE TRIGGER ChnState_log_dest
AFTER INSERT ON CallLog
FOR EACH ROW
WHEN NEW.Dir = 1
BEGIN
    INSERT INTO Setting(Name, Value)
    VALUES('LstChnSt' || (SELECT Id
                          FROM ChnStatus
                          WHERE No = NEW.DestNo),
           NEW.ID);
END;