[sqlite] INSERT OR REPLACE and INSERT/DELETE/UPDATE triggers

[David Barrett]

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