The parent is still being deleted and inserted, however, the trigger now uses the REPLACE conflict resolution method rather than the IGNORE resolution method and that resolution method causes the deletion and insertion of a new child record.
--- 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
