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 -- Best, Pali _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users