You are right that my purpose does seem kind of confusing. What I really want is UPSERT to avoid doing an UPDATE, checking if the number of rows affected is zero, then doing an INSERT. The lack of UPSERT leads to a lot more application code using the UPDATE/INSERT combination. UPSERT doesn't exist in SQLite so I was trying to work around that by using INSERT OR REPLACE which is not the same thing. I can see from another recent thread that some others also think that UPSERT would be a valuable addition to SQLite.
Peter On Fri, Mar 16, 2018 at 2:07 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > It is kind of hard to write a specific trigger since your "purpose" is > confusing. You have defined some referential integrity and are then > ignoring it. If your goal is to update the parent, then why not use the > statement designed to do that (UPDATE) ... ? > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > >-----Original Message----- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux > >Sent: Friday, 16 March, 2018 12:42 > >To: SQLite mailing list > >Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE > >followed by INSERT > > > >Thank you for the information, Keith. > > > >It comes as a surprise to me that the conflict resolution clause of > >the > >statement that causes a trigger to fire can override an explicit > >conflict > >resolution clause in the body of a trigger. But, as you pointed out, > >it is > >documented. > > > >How can I write the trigger to not use a conflict resolution clause? > >I'm > >trying a bunch of different things but with no luck. Something like > >the > >following is possible? > > > >CREATE TRIGGER users_after_insert AFTER INSERT ON users > >BEGIN > > CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id = > >new.id)) = > >0 THEN > > INSERT INTO user_extras (user_id) VALUES (new.id) > > END; > >END; > > > >Thanks. > > > >Peter > > > > > > > >On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf <kmedc...@dessus.com> > >wrote: > > > >> > >> On this page, 7th paragrph: > >> > >> https://www.sqlite.org/lang_createtrigger.html > >> > >> See that: > >> > >> An ON CONFLICT clause may be specified as part of an UPDATE or > >INSERT > >> action within the body of the trigger. However if an ON CONFLICT > >clause is > >> specified as part of the statement causing the trigger to fire, > >then > >> conflict handling policy of the outer statement is used instead. > >> > >> --- > >> The fact that there's a Highway to Hell but only a Stairway to > >Heaven says > >> a lot about anticipated traffic volume. > >> > >> > >> >-----Original Message----- > >> >From: sqlite-users [mailto:sqlite-users- > >> >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux > >> >Sent: Monday, 12 March, 2018 21:09 > >> >To: sqlite-users@mailinglists.sqlite.org > >> >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE > >> >followed by INSERT > >> > > >> >Hi, > >> > > >> >I have read that INSERT OR REPLACE is equivalent to a DELETE > >followed > >> >by an > >> >INSERT. I came across a case where that is not true. > >> > > >> >Set up a test case > >> > > >> > $ rm -f asdf.sqlite && sqlite3 asdf.sqlite > >> > > >> > sqlite> .mode columns > >> > sqlite> .headers on > >> > sqlite> PRAGMA foreign_keys=OFF; > >> > > >> > sqlite> CREATE TABLE users ( > >> > ...> id INT UNSIGNED NOT NULL PRIMARY KEY, > >> > ...> username TEXT NOT NULL > >> > ...> ); > >> > > >> > sqlite> CREATE TABLE user_extras ( > >> > ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY, > >> > ...> other INT NULL DEFAULT NULL, > >> > ...> FOREIGN KEY (user_id) REFERENCES users(id) ON > >UPDATE > >> >CASCADE ON DELETE CASCADE > >> > ...> ); > >> > > >> > sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >> > > >> > sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, > >33); > >> > > >> > sqlite> SELECT * FROM users; > >> > id username > >> > ---------- ---------- > >> > 1 asdf > >> > > >> > sqlite> SELECT * FROM user_extras; > >> > user_id other > >> > ---------- ---------- > >> > 1 33 > >> > > >> > > >> >Let's try a delete followed by an insert. > >> > > >> > sqlite> DELETE FROM users WHERE id = 1; > >> > > >> > sqlite> SELECT * FROM users; > >> > > >> > sqlite> SELECT * FROM user_extras; > >> > user_id other > >> > ---------- ---------- > >> > 1 33 > >> > > >> > sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >> > > >> > sqlite> SELECT * FROM users; > >> > id username > >> > ---------- ---------- > >> > 1 asdf > >> > > >> > sqlite> SELECT * FROM user_extras; > >> > user_id other > >> > ---------- ---------- > >> > 1 33 > >> > > >> >Notice that the value `user_extras.other` is still 33. That's > >good. > >> > > >> > sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, > >> >'asdf'); > >> > > >> > sqlite> SELECT * FROM users; > >> > id username > >> > ---------- ---------- > >> > 1 asdf > >> > > >> > sqlite> SELECT * FROM user_extras; > >> > user_id other > >> > ---------- ---------- > >> > 1 33 > >> > > >> >Still 33. That's good. > >> > > >> >Based on the above, it looks like `INSERT OR REPLACE` is > >equivalent > >> >to a > >> >`DELETE` followed by an `INSERT`. > >> > > >> >If we add a trigger, things change... > >> > > >> > sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON > >users > >> > ...> BEGIN > >> > ...> INSERT OR IGNORE INTO user_extras (user_id) VALUES > >> >(new.id); > >> > ...> END; > >> > > >> >A `DELETE` followed by an `INSERT` still works as before. > >> > > >> > sqlite> DELETE FROM users WHERE id = 1; > >> > > >> > sqlite> SELECT * FROM users; > >> > > >> > sqlite> SELECT * FROM user_extras; > >> > user_id other > >> > ---------- ---------- > >> > 1 33 > >> > > >> > sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >> > > >> > sqlite> SELECT * FROM users; > >> > id username > >> > ---------- ---------- > >> > 1 asdf > >> > > >> > sqlite> SELECT * FROM user_extras; > >> > user_id other > >> > ---------- ---------- > >> > 1 33 > >> > > >> >See above that the value of `user_extras.other` survived the > >`DELETE` > >> >followed by the `INSERT`. > >> > > >> >When we use `INSERT OR REPLACE` the `user_extras.other` value is > >> >cleared > >> >out. > >> > > >> > sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, > >> >'asdf'); > >> > > >> > sqlite> SELECT * FROM users; > >> > id username > >> > ---------- ---------- > >> > 1 asdf > >> > > >> > sqlite> SELECT * FROM user_extras; > >> > user_id other > >> > ---------- ---------- > >> > 1 > >> > > >> >Because of the trigger, `INSERT OR REPLACE` is no longer > >equivalent > >> >to > >> >`DELETE` followed by `INSERT`. > >> > > >> > > >> >Peter > >> >_______________________________________________ > >> >sqlite-users mailing list > >> >sqlite-users@mailinglists.sqlite.org > >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >users > >> > >> > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >users > >> > >_______________________________________________ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
