带有'case when'语句的 sqlite 触发器

Question

我想从日志表中获取每个通道的最后一个插入ID，然后将其写入另一个表。为此，我在日志表上写了一个触发器，但是由于语法错误而行不通。

语法使用用于案例语句的语法，就像 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 

我的代码：

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

一个案例表达式只能在其他表达式之间选择，而不是插入语句。

创建触发器语句的何时经常会有所帮助。对于其他任何事情，您必须将逻辑放在实际语句中，例如：

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;

Answer 2

我不确定这是否是答案，因为它似乎太明显了...每个子Query