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

Reply via email to