Post #2201787

@ratfactor@mastodon.art Saw your sqlite-monotonic-updates card and was thinking that it could be handled quite nicely with triggers. Sorry if someone else has suggested this one. Starting with this table:CREATE TABLE foo (touched int, txt text); We can set up an UPDATE trigger:CREATE TRIGGER foo_touched_update AFTER UPDATE ON FOO BEGIN UPDATE foo SET touched = coalesce((SELECT touched FROM foo ORDER BY touched DESC LIMIT 1), 0)+1 WHERE rowid=new.rowid; END; But we'd also need some way to set touched on an INSERT. A DEFAULT clause won't work because it's not constant, but we can force an UPDATE on basically any field we want which will trigger our update trigger:CREATE TRIGGER foo_touched_insert AFTER INSERT ON FOO BEGIN UPDATE FOO SET touched=0 WHERE rowid=new.rowid; END; And now when we do any INSERTs or UPDATEs, we don't need to specify touched explicitly. Here's what the INSERT, SELECT and UPDATE clauses look like:insert into foo(txt) values ('Hello world!'); insert into foo(txt) values ('Can of beans'); select rowid, touched, txt from foo; update foo set txt='Can of worms' where rowid=2; select rowid, touched, txt from foo order by touched desc; insert into foo(txt) values ('Bag of rocks.'); select rowid, touched, txt from foo order by touched desc; update foo set txt='Can of snakes.' where rowid=2; select rowid, touched, txt from foo order by touched desc; Lastly, as usual, if you're expecting this to have lots of rows, an index over the touched field is worthwhile:CREATE INDEX foo_idx_touched ON foo(touched);That way SQLite can do a scan over as many rows of the index as you have specified in any LIMIT clause, instead of having to scan the whole table to set up a temporary B-tree for ordering. (EXPLAIN QUERY PLAN is your friend as usual.)