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-
>[email protected]] On Behalf Of Clemens Ladisch
>Sent: Sunday, 26 August, 2018 06:56
>To: [email protected]
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
