With this table
create table FOLDER ( _ID integer primary key autoincrement, NAME text not null, PARENT integer not null, DELETED integer, constraint VALUEOF_folder_deleted check (DELETED == 1 or DELETED isnull) on conflict abort, unique (NAME, PARENT) on conflict abort );
I wanted to replace an insert where the NAME PARENT combination exists and DELETED is set to 1 with a replace setting the DELETED field to null.
I tried this trigger:
CREATE TRIGGER REPLACE_INS_folder instead of insert on FOLDER when exists (select 1 from FOLDER where NAME == new.NAME and PARENT == new.PARENT and DELETED == 1) begin update FOLDER set DELETED = null where NAME == new.NAME and PARENT == new.PARENT; end;
Error: cannot create INSTEAD OF trigger on table: FOLDER
Initially I had a syntax error in the trigger, but received the same error, suggesting that there is something more restrictive going on. The diagram in this documentation https://www.sqlite.org/lang_createtrigger.html suggests my trigger is valid. However, the second part of the text
A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever an UPDATE occurs on on one or more specified columns of a table. makes me wonder if WHEN is only allowed with update triggers.
Note this is a trigger on a table, not on a view.