conditional insert statement in sqlite triggers
That is an syntax error as the Syntax Diagram for SQLite Triggers does not allow any IF clauses nor CASE WHEN constructions.
But you can achieve the same effect by defining two or three triggers that use the WHEN condition, see http://sqlite.org/lang_createtrigger.html
So you would create on trigger for your DELETE case like this:
CREATE TRIGGER delete_from_other_table AFTER INSERT ON someTableWHEN new.someValue = 0BEGIN DELETE FROM anotherTable WHERE (... some condition );END;And than add another trigger for the INSERT and UPDATE Case with the appropriate conditions...
CREATE TRIGGER update_another_table AFTER INSERT ON someTableWHEN new.someValue <> 0BEGIN ...END;
Below is an example of a trigger with subselect:
Tables
CREATE TABLE A( ID INTEGER PRIMARY KEY, NAME TEXT)CREATE TABLE LOG( ID INTEGER PRIMARY KEY, DT TEXT)
Created trigger with sub select
CREATE TRIGGER new_lns_historic AFTER INSERT ON A WHEN NEW.ID NOT IN (SELECT ID FROM LOG)BEGIN INSERT INTO LOG VALUES (NEW.ID, DATETIME('NOW'));END