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 <[email protected]> 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-
> >[email protected]] On Behalf Of Peter Michaux
> >Sent: Monday, 12 March, 2018 21:09
> >To: [email protected]
> >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
> >[email protected]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
