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 <csanyi...@gmail.com> 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
