A before trigger which uses the raise function would stop it from getting inserted in the first place.
create trigger cull before insert on tbl when new.value = (select value from tbl order by time desc limit 1) begin select raise(ignore); end; Or if you want it to actually return an error to let you know what happened you could make it select raise(abort, 'Repeated entry'); -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Przemek Klosowski Sent: Tuesday, February 25, 2020 12:02 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] Fwd: inserting new data only I am storing time series data arriving from a sensor into (time,value) records, like so: 10:32 12 10:35 15 10:37 15 10:39 13 10:43 13 10:46 18 and I want to avoid storing repetitive data, so that the database should contain 10:32 12 10:35 15 10:39 13 10:46 18 where only the earliest time with the unchanging value is stored. I don't see how INSERT could be conditional on e.g. value != (select value from tbl order by time descending limit 1), so I thought I'd use triggers. The only way I could think of was to delete the new duplicate record after it has been inserted: create trigger cull after insert on tbl when (select value-lead(value) over (order by time desc) from a limit 1) = 0 begin delete from a where time like new.time; end; Is there a simpler way? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
