Jeuwjedmj unnecessary screenshots she's w so I whhn whhn whhn Bassi uaaiuaai USA xqhqs? ---------- Forwarded message ---------- From: "David Barrett" <dbarr...@expensify.comb-) Date: May 31, 2015 12:44 PMfewjf Fjcewhcdwxwhcwdudw hewahfu??:-$ :-$ :-$ :-$ :-$ :-$ ?????????????????????????????aIjjaPp!p!-) B-) B-) B-) ??? ? Subject: Re: [sqlite] INSERT OR REPLACE and INSERT/DELETE/UPDATE triggers To: "General Discussion of SQLite Database" < sqlite-users at mailinglists.sqlite.org>L Cc:
> 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 > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
