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;

I am not sure if this is the answer as it seems too obvious... the word WHERE is misspelt in every sub-query. — Brendan
– Brendan, Commented Jan 22, 2014 at 14:37
I will add this as the answer then - it would be appreciated if you mark it as the correct one. Pleased you are sorted. — Brendan
– Brendan, Commented Jan 23, 2014 at 7:51
@Brendan As CL. said, the problem of query was for CASE expression. — M-Razavi
– M-Razavi, Commented Dec 2, 2017 at 6:24

CL. · Accepted Answer · 2014-01-22 17:50:30Z

7

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;

answered Jan 22, 2014 at 17:50

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

skeetastax Over a year ago

Is FOR EACH ROW always required in a PostgreSQL TRIGGER?

CL. Over a year ago

@skeetastax This question is about SQLite. The PostgreSQL trigger syntax is entirely different.

Brendan · Accepted Answer · 2014-01-23 07:52:06Z

0

I am not sure if this is the answer as it seems too obvious... the word WHERE is misspelt in every sub-query

answered Jan 23, 2014 at 7:52

Brendan

1,2451 gold badge16 silver badges34 bronze badges

1 Comment

Wolf Over a year ago

I think your former comment solved the issue in wording the question. As obvious, the core of the problem isn't affected by this. OP corrected his misspelling, so there is no need for this answer.

Collectives™ on Stack Overflow

Sqlite trigger with 'case when' statement

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related