Proper syntax for "CASE WHEN ... THEN ... DELETE ... END" in SQLite triggers?

Question

I'm trying to figure out how to put a delete statement inside a conditional in an SQLite trigger.

I've come up with this example code:

CREATE TRIGGER mytrigger
    BEFORE INSERT ON mytable
BEGIN
    SELECT CASE WHEN 
        1 == 1
    THEN
        DELETE FROM mytable
    END;
END;

But it fails to compile with:

Error: near "DELETE": syntax error

If I replace DELETE FROM mytable with RAISE(FAIL, "mytrigger was activated"), it compiles fine.

Answer 1

AFAIK SQLite does not support conditional execution. The CASE expression is an expression(not a statement!) for conditional evaluation(not execution!). That means you can use it to choose what value to return - but not what statements to execute.

The bad news is that SQLite can't decide whether to execute the DELETE statement or not. The good news is that you don't really care if the DELETE statement is executed - you only care if the rows are deleted. So - always execute the DELETE statement, but make it delete rows only if your condition returns true:

CREATE TRIGGER mytrigger
    BEFORE INSERT ON mytable
BEGIN
    DELETE FROM mytable
    WHERE 1 == 1
END;