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 someTable
WHEN new.someValue = 0
BEGIN
   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 someTable
WHEN new.someValue <> 0
BEGIN
   ...
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