create trigger users_after_insert after insert on users when not exists ( select 1 from user_extras where user_id = new.id ) begin insert into user_extras (user_id) values (new.id); end;
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux Sent: Friday, March 16, 2018 2:42 PM 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
