On Tue, Feb 25, 2020 at 12:22 PM David Raymond <david.raym...@tomtom.com> wrote: > > 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');
Ah, that's cool---I missed the 'ignore' possibility and thought that the before trigger can't prevent the subsequent insert. > > > -----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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
