create table templog ( datestamp text collate nocase primary key, centTemp REAL not null, fahrTemp REAL );
create trigger TempLogInsert after insert on templog begin update templog set fahrtemp = new.centTemp/9*5+32 where datestamp == new.datestamp; end; create trigger tempLogUpdate after update of centTemp on tempLog begin update templog set fahrtemp = new.centTemp/9*5+32 where datestamp == old.datestamp; end; There ya go. A persistent computed column ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Andy Goth >Sent: Saturday, 9 June, 2018 19:18 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Idea: defining table-valued functions directly >in SQL > >On 06/09/18 20:10, Simon Slavin wrote: >> On 10 Jun 2018, at 2:00am, Andy Goth <andrew.m.g...@gmail.com> >wrote: >>> CREATE TABLE tempLog ( >>> datestamp TEXT COLLATE NOCASE PRIMARY KEY >>> , centTemp REAL); >>> CREATE VIEW tempLogView AS >>> SELECT * >>> , centTemp * 9 / 5 + 32 AS fahrTemp >>> FROM tempLog; >> >> Yes. That is an elegant and compact way to do it with SQLite as it >is >> now. But it's not as satisfying. And it doesn't allow indexing by >> the computed column. You have to define the calculation in the >index >> separately. > >However, views make behavior of INSERT and UPDATE clear, since they >can >only operate on the real table. INSERT or UPDATE become murky when >in >the presence of computed columns. I suppose the only sane thing to >do >is forbid directly setting the value of a computed column, though >what >would the syntax be? Skip computed columns in the value list? If >two >tables have the same schema, this should duplicate one into the >other, >but apparently not: > >INSERT INTO table2 SELECT * from table1; > >-- >Andy Goth | <andrew.m.goth/at/gmail/dot/com> >_______________________________________________ >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
