Hello,
I guess there is something I'm missing when trying to synchronize some
data with a trigger.
How do I get the trigger to fire when the comparison in either new.x or
old.x is null?
The below tested with 3.8.6 sqlite command line shell:
CREATE TABLE table1(
id INTEGER PRIMARY KEY,
value INT
);
CREATE TABLE table1_mirror(
id INT,
value INT
);
CREATE TRIGGER trigger_1
AFTER UPDATE OF value ON table1
WHEN (new.value != old.value)
BEGIN
UPDATE table1_mirror
SET value = new.value
WHERE id = new.id
;
END;
INSERT INTO table1 VALUES(1, 1);
INSERT INTO table1_mirror VALUES(1,1);
UPDATE table1 SET value = 2 WHERE id = 1;
SELECT value from table1; -- 2
SELECT value from table1_mirror; -- 2
UPDATE table1 SET value = 3 WHERE id = 1;
SELECT value from table1; -- 3
SELECT value from table1_mirror; -- 3
UPDATE table1 SET value = null WHERE id = 1;
SELECT value from table1; -- null
SELECT value from table1_mirror; -- still 3 , why not null?
UPDATE table1 SET value = 4 WHERE id = 1;
SELECT value from table1; -- 4
SELECT value from table1_mirror; -- still 3 , why not 4?
UPDATE table1 SET value = 5 WHERE id = 1;
SELECT value from table1; --5
SELECT value from table1_mirror; --5
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
