triggers - 带有 'case when' 语句的 SQLite 触发器

Question

我想从日志表中获取每个 channel 最后插入的ID，然后将其写入另一个表。为此，我在日志表上编写了一个触发器，但由于语法错误，它不起作用。

用于 case 语句的语法，与 Sqlite 引用完全相同。

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 

我的代码:

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

CASE 表达式只能用于在其他表达式之间进行选择，不能用于 INSERT 等语句。

CREATE TRIGGER 语句的 WHEN 子句通常会有所帮助。对于其他任何事情，您必须将逻辑放入实际语句中，如下所示:

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;