Ah, naturally I found the answer right after sending this. The secret: PRAGMA recursive_triggers = 1;
As stated here: https://www.sqlite.org/lang_conflict.html "When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled." Re-running my test with this shows it causes the delete trigger to fire as expected: sqlite> PRAGMA recursive_triggers = 1; sqlite> insert or replace into nvps values ( 'name1', 'value4' ); sqlite> select * from nvps; name1|value4 sqlite> select * from actions; inserted "name1", "value1" inserted "name2", "value2" updated "name2" from "value2" to "value2.1" deleted "name2", "value2.1" deleted "name1", "value1" inserted "name1", "value4" sqlite> Awesome. Go sqlite! -david On Sat, May 30, 2015 at 11:33 PM, David Barrett <dbarrett at expensify.com> wrote: > I fear I already know the answer to this, but I want to ask to make sure: > is there ANY way to craft a trigger that can detect when an INSERT OR > REPLACE trigger does a REPLACE versus an INSERT? I found this thread which > suggested it wasn't possible: > > > http://sqlite.1065341.n5.nabble.com/Trigger-logic-with-INSERT-OR-REPLACE-td65541.html > > But it wasn't totally clear to me, so I tried to construct a test case > myself -- and to my dismay, it confirmed (so far as I can tell) that a > REPLACE looks to the trigger exactly like an INSERT. Is there any trick > I'm overlooking, or is that just a current limitation of sqlite? > > The test I ran follows: > > Davids-MacBook-Air:~ dbarrett$ sqlite3 > SQLite version 3.8.5 2014-08-15 22:37:57 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> /* ----------------------------------------- > ...> Create a simple name/value pair table > ...> ------------------------------------------ */ > sqlite> create table nvps ( name primary key, value ); > sqlite> > sqlite> /* ----------------------------------------- > ...> Create a table of "actions" performed on > ...> the NVP table > ...> ------------------------------------------ */ > sqlite> create table actions ( actions ); > sqlite> > sqlite> /* ----------------------------------------- > ...> Create the triggers to log actions > ...> ------------------------------------------ */ > sqlite> create trigger onInsert after insert on nvps > ...> begin > ...> insert into actions values ('inserted "' || new.name || '", "' || > new.value || '"' ); > ...> end; > sqlite> > sqlite> create trigger onUpdate after update on nvps > ...> begin > ...> insert into actions values ('updated "' || new.name || '" from "' > || old.value || '" to "' || new.value || '"' ); > ...> end; > sqlite> > sqlite> create trigger onDelete after delete on nvps > ...> begin > ...> insert into actions values ('deleted "' || old.name || '", "' || > old.value || '"' ); > ...> end; > sqlite> > sqlite> /* ----------------------------------------- > ...> Insert a couple values; note that the > ...> triggers work as expected in all cases. > ...> ------------------------------------------ */ > sqlite> insert into nvps values ( 'name1', 'value1' ); > sqlite> insert into nvps values ( 'name2', 'value2' ); > sqlite> update nvps set value='value2.1' where name='name2'; > sqlite> delete from nvps where name='name2'; > sqlite> select * from nvps; > name1|value1 > sqlite> select * from actions; > inserted "name1", "value1" > inserted "name2", "value2" > updated "name2" from "value2" to "value2.1" > deleted "name2", "value2.1" > sqlite> > sqlite> /* ----------------------------------------- > ...> Attempt to insert a non-unique value into > ...> a column with a unique constraint -- note > ...> how it fails. > ...> ------------------------------------------ */ > sqlite> insert into nvps values ( 'name1', 'value3' ); > Error: UNIQUE constraint failed: nvps.name > sqlite> > sqlite> /* ----------------------------------------- > ...> Finally, attempt the same insert, but with > ...> an "or replace". Note how it works, and > ...> correctly logs the the insert, but doesn't > ...> also log a delete. In other words, no > ...> trigger fired that indicates we replaced a > ...> value. > ...> ------------------------------------------ */ > sqlite> insert or replace into nvps values ( 'name1', 'value4' ); > sqlite> select * from nvps; > name1|value4 > sqlite> select * from actions; > inserted "name1", "value1" > inserted "name2", "value2" > updated "name2" from "value2" to "value2.1" > deleted "name2", "value2.1" > inserted "name1", "value4" > sqlite> > > Any suggestions? Thanks! > > -david >
