On Thu, 13 Oct 2016 11:45:14 +0200, Daniel Polski
<[email protected]> wrote:
That's because the expression in the WHEN clause
(new.value != old.value)
is not valid when one of them is NULL.
HTH
--
Regards,
Kees Nuyt
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
