Well, the documentation *says* that a with clause cannot be used in a trigger, but that is incorrect, at least for the current tip of trunk ... because the following script works ...
---//--- snip ---//--- pragma recursive_triggers = 1; create table if not exists services ( id INTEGER PRIMARY KEY, startDate text not null, endDate text null, years integer null, months integer null, days integer null, toggle integer default (-1) ); create trigger if not exists InsertServices after insert on services begin update services set toggle = -new.toggle where id = new.id; end; create trigger if not exists UpdateServices after update of startDate, endDate, toggle on services begin update services set (years, months, days) = (with recursive dates (startDate, endDate) as ( select coalesce(new.startDate, date('now', 'localtime')), coalesce(new.endDate, date('now','localtime')) ), yearsTable (startDate, years, months, days, resultDate, endDate) as ( select min(startDate, endDate), 0, 0, 0, min(startDate, endDate), max(startDate, endDate) from dates union all select startDate, years + 1, months, days, date(startDate, printf('%+d years', years + 1)), endDate from yearsTable where resultDate < endDate ), monthsTable (startDate, years, months, days, resultDate, endDate) as ( select * from ( select * from yearsTable where resultDate <= endDate order by years desc, months desc, days desc limit 1 ) union all select startDate, years, months + 1, days, date(startDate, printf('%+d years', years), printf('%+d months', months + 1)), endDate from monthsTable where resultDate < endDate ), daysTable (startDate, years, months, days, resultDate, endDate) as ( select * from ( select * from monthsTable where resultDate <= endDate order by years desc, months desc, days desc limit 1 ) union all select startDate, years, months, days + 1, date(startDate, printf('%+d years', years), printf('%+d months', months), printf('%+d days', days + 1)), endDate from daysTable where resultDate < endDate ), dateDifference (startDate, resultDate, years, months, days) as ( select startDate, resultDate, years, months, days from daysTable where resultDate = endDate ) select years, months, days from dateDifference ) where id = new.id; end; .mode col .head on insert into services (startDate) values ('1995-02-01'); insert into services (startDate, endDate) values ('1995-02-01', '2018-08-31'); insert into services (startDate) values ('2004-02-01'); insert into services (startDate, endDate) values ('2004-02-01', '2018-08-31'); select * from services; -- when run this recalculates all rows where the endDate is null using the current date update services set toggle=-toggle where endDate is null; ---//--- snip ---//--- --- 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 Clemens Ladisch >Sent: Sunday, 26 August, 2018 06:56 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Why trigger (UPDATE) does not work? > >Csányi Pál wrote: >> CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON >MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = ( with >... > ><https://www.sqlite.org/lang_with.html> says: >| The WITH clause cannot be used within a CREATE TRIGGER. > > >Regards, >Clemens >_______________________________________________ >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