[sqlite] INSERT OR REPLACE and INSERT/DELETE/UPDATE triggers
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
> 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 * fro
[sqlite] INSERT OR REPLACE and INSERT/DELETE/UPDATE triggers
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
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] INSERT OR REPLACE and INSERT/DELETE/UPDATE triggers
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
