On Sun, Aug 26, 2018 at 07:45:33AM -0400, Brian Curley wrote:
> You don't list your trigger definition if there's anything that might need
> troubleshooting, but I will say that I've recently stopped using
> SQLiteStudio for its somewhat erratic behavior.
I attached to my previous mail the whole database dump in which is
that trigger which does not work as I expected.
But here is then the database itself:
CREATE TABLE MyLengthOfService (id INT PRIMARY KEY, WorkPlaceName TEXT,
StartDate DATE, EndDate DATE, WithWorkingTime INT, Comment TEXT, Years INT,
RemainingMonths INT, RemainingDays INT);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate,
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (1,
'Name of the 1. work place', '1983-07-11', '1984-08-31', 1, 'workman', 1, 1,
21);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate,
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (2,
'Name of the 2. work place', '1984-11-01', '1986-01-15', 1, 'workman', 1, 2,
15);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate,
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (3,
'Name of the 3. work place', '1986-01-16', '1999-07-16', 1, 'workman', 13, 6,
1);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate,
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (4,
'Name of the 4. work place', '2000-02-01', '2000-08-31', 1, 'teacher', 0, 7, 0);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate,
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (5,
'Name of the 4. work place', '2000-09-01', '2001-01-31', 0.5,'teacher', 0, 5,
0);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate,
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (6,
'Name of the 4. work place', '2001-02-01', '2018-08-26', 1, 'teacher', 17, 6,
26);
CREATE TABLE SummedYearsMonthsDays (
id INT PRIMARY KEY,
SummedYears INT,
RemainingSummedMonths INT,
RemainingSummedDays INT
);
INSERT INTO SummedYearsMonthsDays (id, SummedYears, RemainingSummedMonths,
RemainingSummedDays) VALUES (1, 12, 0, 0);
CREATE TRIGGER AllYearsMonthsDays AFTER UPDATE OF Years, RemainingMonths,
RemainingDays ON MyLengthOfService BEGIN UPDATE SummedYearsMonthsDays SET
RemainingSummedDays = CASE WHEN ( SELECT total(RemainingDays) FROM
MyLengthOfService ) < 30 THEN RemainingSummedDays = ( SELECT
total(RemainingDays) FROM MyLengthOfService ) ELSE RemainingSummedDays = (
SELECT total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
UPDATE SummedYearsMonthsDays SET RemainingSummedMonths = CASE WHEN ( SELECT
total(RemainingDays) FROM MyLengthOfService ) < 30 THEN RemainingSummedMonths =
( SELECT total(RemainingMonths) FROM MyLengthOfService ) ELSE
RemainingSummedMonths = ( SELECT CAST ( total(RemainingMonths) +
total(RemainingMonths) / 30 AS INTEGER ) FROM MyLengthOfService ) END WHERE id
= 1; END;
CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON
MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = ( with recursive
dates (startDateR, endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate)
FROM MyLengthOfService WHERE EndDate = NEW.EndDate), yearsTable (startDateR,
years, months, days, resultDate, endDateR) as ( select min(startDateR,
endDateR), 0, 0, 0, min(startDateR, endDateR), max(startDateR, endDateR) from
dates union all select startDateR, years + 1, months, days, date(startDateR,
'+' || cast(years + 1 as text) || ' years'), endDateR from yearsTable where
resultDate < endDateR ), monthsTable (startDateR, years, months, days,
resultDate, endDateR) as ( select * from ( select *from yearsTable where
resultDate <= endDateR order by years desc, months desc, days desc limit 1)
unionall select startDateR, years, months + 1, days, date(startDateR, '+' ||
cast(years as text) || ' years', '+' || cast(months + 1 as text) || ' months'),
endDateR from monthsTable where resultDate < endDateR ), daysTable (startDateR,
years, months, days, resultDate, endDateR) as ( select * from( select * from
monthsTable where resultDate <= endDateR order by years desc, months desc, days
desc limit 1) union all select startDateR, years, months, days + 1,
date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months as
text) || ' months', '+' || cast(days + 1 as text) || ' days'), endDateR from
daysTable where resultDate < endDateR ) select years from daysTable where
resultDate = endDateR ), RemainingMonths = ( with recursive dates (startDateR,
endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate) FROM
MyLengthOfService WHERE EndDate = NEW.EndDate), yearsTable (startDateR, years,
months, days, resultDate, endDateR) as ( select min(startDateR, endDateR), 0,
0, 0, min(startDateR, endDateR), max(startDateR,endDateR) from dates union all
select startDateR, years + 1, months, days, date(startDateR, '+' || cast(years
+ 1 as text) || ' years'), endDateR from yearsTable where resultDate < endDateR
), monthsTable (startDateR, years, months, days, resultDate, endDateR) as (
select * from ( select * from yearsTable where resultDate <= endDateR order by
years desc, months desc, days desc limit 1) union all select startDateR, years,
months + 1, days, date(startDateR, '+' || cast(years as text) || ' years', '+'
|| cast(months + 1 as text) || ' months'), endDateR from monthsTable where
resultDate < endDateR ), daysTable (startDateR, years, months, days,
resultDate, endDateR) as ( select * from( select * from monthsTable where
resultDate <= endDateR order by years desc, months desc, days desc limit 1)
union all select startDateR, years, months, days + 1, date(startDateR, '+' ||
cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+'
|| cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate
< endDateR ) select months from daysTable where resultDate = endDateR ),
RemainingDays = ( with recursive dates (startDateR, endDateR) as (SELECT
date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE EndDate =
NEW.EndDate), yearsTable (startDateR, years, months, days, resultDate,
endDateR) as ( select min(startDateR, endDateR), 0, 0, 0, min(startDateR,
endDateR), max(startDateR, endDateR) from dates union all select startDateR,
years + 1, months, days, date(startDateR, '+' || cast(years + 1 as text) ||'
years'), endDateR from yearsTable where resultDate < endDateR ), monthsTable
(startDateR, years, months, days, resultDate, endDateR) as ( select * from (
select * from yearsTable where resultDate <= endDateR order by years desc,
months desc, days desc limit 1) union all select startDateR, years, months + 1,
days, date(startDateR, '+' || cast(years as text) || ' years', '+' ||
cast(months + 1 as text) || ' months'), endDateR from monthsTable where
resultDate < endDateR ), daysTable (startDateR, years, months, days,
resultDate, endDateR) as ( select * from( select * from monthsTable where
resultDate <= endDateR order by years desc, months desc, days desc limit 1)
union all select startDateR, years, months, days + 1, date(startDateR, '+' ||
cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+'
||cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate
< endDateR ) select days from daysTable where resultDate = endDateR ) WHERE
EndDate = NEW.EndDate; END;
The question is: why does not work the AllYearsMonthsDays trigger as I
expecting?
> Regards.
>
> Brian P Curley
>
>
> On Sun, Aug 26, 2018, 3:41 AM Csányi Pál <[email protected]> wrote:
>
> > Hi,
> >
> > I have a small database with schema attached in this mail.
> > I have two triggers to update fields after an update occures.
> >
> > When I run the SQL code in sqlite3 CLI, or in SqliteStudio's SQL
> > Editor, it output is that I expect. It gives the numbers of months and
> > days so far:
> > SELECT total(RemainingDays) FROM MyLengthOfService;
> > total(RemainingDays)
> > 63
> >
> > SELECT total(RemainingDays) % 30 FROM MyLengthOfService;
> > total(RemainingDays) % 30
> > 3
> >
> > But even if I run the trigger in SqliteStudio's SQL Editor alone, it
> > does not give the expected output:
> > UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN (
> > SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN
> > RemainingSummedDays = ( SELECT total(RemainingDays) FROM
> > MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT
> > total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
> > The output is empty.
> >
> > When the trigger is fired, those numbers are not updated in the
> > corresponding fields of the corresponding table.
> > To be more precise, the AllYearsMonthsDays trigger does not work.
> > The YearsRemainingMonthsDays trigger works.
> >
> > What am I missing here?
> >
> > --
> > Best, Pali
--
Best, Pali
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
