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
