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