[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.

2016-02-17 Thread 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
> (
>   idTEXT 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.

[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.

2016-02-17 Thread Paul 

Oh, thanks for pointing me to this statement! 
Didn't know that 'OR IGNORE' is an alias of 'ON CONFLICT IGNORE'.


17 February 2016, 17:32:32, by "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
> > (
> >   idTEXT 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.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[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.

2016-02-17 Thread Paul 
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); 

SELECT * FROM foo; 
id??? 
-- 
1 
2 
3 

SELECT * FROM properties; 
name value 
---? -- 
foo_inserts? 1? 

The results are not very much expected. If I try different example, again with 
a clean database I get: 

INSERT INTO foo(id) VALUES(1); 
INSERT INTO foo(id) VALUES(2); 
INSERT INTO foo(id) VALUES(3); 

SELECT * FROM foo; 
id??? 
-- 
1 
2 
3? 

SELECT * FROM properties; 
name value 
---? -- 
foo_inserts? 3 

Everything works perfectly. This is also true for INSERT OR IGNORE INTO 
foo. 

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'.