[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

[Dan Kennedy]

On 02/17/2016 08:47 PM, Paul wrote:
> Let's say I have a following database structure:
>
> CREATE TABLE properties
> (
>   name              TEXT NOT NULL,
>   value             TEXT,
>   PRIMARY KEY(name)
> ) WITHOUT ROWID;
>
> CREATE TABLE foo
> (
>   id                TEXT NOT NULL,
>   PRIMARY KEY(id)
> );
>
> CREATE TRIGGER foo_inserted
>    AFTER INSERT ON foo
>   BEGIN
>     INSERT OR IGNORE INTO properties(name, value) VALUES('foo_inserts', 0);
>     UPDATE properties SET value = value + 1 WHERE name = 'foo_inserts';
>   END;
>
> With a clean database I perform set of queries:
>
> INSERT OR REPLACE INTO foo(id) VALUES(1);
> INSERT OR REPLACE INTO foo(id) VALUES(2);
> INSERT OR REPLACE INTO foo(id) VALUES(3);
>
...
> I've made different test cases and came to a conclusion that 'OR IGNORE' 
> clause inside
> a query within a body of trigger suddenly works as if it was 'OR REPLACE'.

The ON CONFLICT clause of the outer statement overrides the ON CONFLICT 
clause of the statement within the trigger:

https://www.sqlite.org/mark/lang_createtrigger.html?However+if+an*used+instead

(scroll the page down a bit to see the highlighted statement)

Dan.