Re: [sqlite] Why trigger (UPDATE) does not work?

[Keith Medcalf]

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,
daysinteger 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

Re: [sqlite] Why trigger (UPDATE) does not work?

[R Smith]

On 2018/08/26 8:44 PM, Csányi Pál wrote:

On Sun, Aug 26, 2018 at 07:17:00PM +0200, R Smith wrote:

You have misunderstand the purpose of the MyLengthOfService table.
The MyLengthOfService table contains WorkPlaces not for many persons,
but for one person only.

So my goal is to get summed years, months and days for that one
person. The person has been worked on many places.



Great, that's even easier:

WITH worktime(grp, totDays) AS (
  SELECT WorkPlaceName, SUM(CAST(strftime('%J',date(EndDate)) AS 
INT)-CAST(strftime('%J',date(StartDate)) AS INT))

    FROM MyLengthOfService
   GROUP BY WorkPlaceName
)
SELECT grp AS WorkPlace, (totDays / 365) AS Years, ((totDays % 365) / 
30) AS Months, ((totDays % 365) % 30) AS Days, totDays

  FROM worktime
;

And, can be made into a VIEW if you like.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why trigger (UPDATE) does not work?

[Csányi Pál]

On Sun, Aug 26, 2018 at 07:17:00PM +0200, R Smith wrote:
> On 2018/08/26 2:16 PM, Csányi Pál wrote:
> >
> >>> 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
> 
> Many things are wrong with those triggers.
> They can't use CTE's, the CASE statements evaluate equations, not 
> values, so they have Boolean results, and one desperate space is missing 
> after a union all statement, (to name the obvious ones) but then more 
> hurtful to my OCD is the repetitive adding of CTEs, each of which 
> evaluates 3 values of which, on every iteration, only 1 value is updated 
> in the table - not to mention that the table is pressed into service as 
> a VIEW, and the very expensive ON UPDATE trigger is filling in the table 
> values that would be much more salient and simple with a VIEW.
> 
> Ok, enough about what is wrong with it. Here's how it can be fixed:
> 
> First, get rid of the triggers, completely.
> Then get rid of the columns in the table named: Years, RemainingMonths, 
> and RemainingDays.
> 
> This will leave you with a Table scripted like this (I added Johnny to 
> test future end-of-service dates):
> 
> CREATE TABLE "MyLengthOfService" (
>    "id" INT PRIMARY KEY,
>    "WorkPlaceName" TEXT,
>    "StartDate" DATE,
>    "EndDate" DATE,
>    "WithWorkingTime" INT,
>    "Comment" TEXT
> );
> INSERT INTO "MyLengthOfService" 
> ("id","WorkPlaceName","StartDate","EndDate","WithWorkingTime","Comment") 
> VALUES
>   (1,'Name of the 1. work place','1983-07-11','1984-08-31',1,'workman')
> ,(2,'Name of the 2. work place','1984-11-01','1986-01-15',1,'workman')
> ,(3,'Name of the 3. work place','1986-01-16','1999-07-16',1,'workman')
> ,(4,'Name of the 4. work place','2000-02-01','2000-08-31',1,'teacher')
> ,(5,'Name of the 4. work place','2000-09-01','2001-01-31',0.5,'teacher')
> ,(6,'Name of the 4. work place','2001-02-01','2018-08-26',1,'teacher')
> ,(7,'Johnny','2018-05-01','2019-04-30',1,'workman')
> ;

You have misunderstand the purpose of the MyLengthOfService table.
The MyLengthOfService table contains WorkPlaces not for many persons,
but for one person only.

So my goal is to get summed years, months and days for that one
person. The person has been worked on many places.

-- 
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why trigger (UPDATE) does not work?

[Simon Slavin]

On 26 Aug 2018, at 6:17pm, R Smith  wrote:

> Ok, enough about what is wrong with it. Here's how it can be fixed:

And there you have it, ladies and gentlemen.  Around two thousand bucks of 
consultancy for free.  The difference between copy-and-paste and actually 
understanding the products in use.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why trigger (UPDATE) does not work?

[R Smith]

On 2018/08/26 2:16 PM, Csányi Pál wrote:



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


Many things are wrong with those triggers.
They can't use CTE's, the CASE statements evaluate equations, not 
values, so they have Boolean results, and one desperate space is missing 
after a union all statement, (to name the obvious ones) but then more 
hurtful to my OCD is the repetitive adding of CTEs, each of which 
evaluates 3 values of which, on every iteration, only 1 value is updated 
in the table - not to mention that the table is pressed into service as 
a VIEW, and the very expensive ON UPDATE trigger is filling in the table 
values that would be much more salient and simple with a VIEW.


Ok, enough about what is wrong with it. Here's how it can be fixed:

First, get rid of the triggers, completely.
Then get rid of the columns in the table named:  Years, RemainingMonths, 
and RemainingDays.


This will leave you with a Table scripted like this (I added Johnny to 
test future end-of-service dates):


CREATE TABLE "MyLengthOfService" (
  "id" INT PRIMARY KEY,
  "WorkPlaceName" TEXT,
  "StartDate" DATE,
  "EndDate" DATE,
  "WithWorkingTime" INT,
  "Comment" TEXT
);
INSERT INTO "MyLengthOfService" 
("id","WorkPlaceName","StartDate","EndDate","WithWorkingTime","Comment") 
VALUES

 (1,'Name of the 1. work place','1983-07-11','1984-08-31',1,'workman')
,(2,'Name of the 2. work place','1984-11-01','1986-01-15',1,'workman')
,(3,'Name of the 3. work place','1986-01-16','1999-07-16',1,'workman')
,(4,'Name of the 4. work place','2000-02-01','2000-08-31',1,'teacher')
,(5,'Name of the 4. work place','2000-09-01','2001-01-31',0.5,'teacher')
,(6,'Name of the 4. work place','2001-02-01','2018-08-26',1,'teacher')
,(7,'Johnny','2018-05-01','2019-04-30',1,'workman')
;


Then, add this View:

CREATE VIEW MyLengthOfServiceTotal AS
WITH worktime(id, dStart, dEnd) AS (
  SELECT id, date(StartDate), date(EndDate) FROM MyLengthOfService
), timediff(id, years, months, days) AS (
  SELECT id,
  CAST(strftime('%Y',dEnd) AS INT)-CAST(strftime('%Y',dStart) AS INT),
  CAST(strftime('%m',dEnd) AS INT)-CAST(strftime('%m',dStart) AS INT),
  CAST(strftime('%d',dEnd) AS INT)-CAST(strftime('%d',dStart) AS INT)
    FROM worktime
), timefix(id, years, months, days) AS (
  SELECT id,
  CASE WHEN years  < 0 THEN 0 WHEN months < 0 THEN years-1 ELSE 
years END,

  CASE WHEN months < 0 THEN months+12 ELSE months END,
  CASE WHEN days   < 0 THEN days+30   ELSE days END
    FROM timediff
), post(id, years, months, days) AS (
  SELECT id,
  CASE WHEN days>29 AND months = 11 THEN years+1 ELSE years END,
  CASE WHEN days>29 THEN months+1 ELSE months END,
  CASE WHEN days>29 THEN 0 ELSE days+1 END
    FROM timefix
)
SELECT post.id, post.years AS TotalYears, post.months AS TotalMonths, 
post.days AS TotalDays

  FROM post

The workings is simple: it calculates the differences in years, months 
and days between the starting and ending dates in the timediff CTE. Then 
it corrects those for overflow in the timefix CTE, and then lastly the 
post CTE is a processing step for some rules that are specific to your 
case (like adding a day, calling 30+ days a month, etc.).


This will now allow you to query things like this:

SELECT A.*, B.TotalYears, B.TotalMonths, B.TotalDays
FROM MyLengthOfService AS A
  JOIN MyLengthOfServiceTotal AS B ON B.id = A.id
;
  -- |   | |    | WithWor- 
| | Total- | Total- | Total-
  --  id | WorkPlaceName | StartDate  | EndDate    | 
kingTime | Comment | Years  | Months |  Days
  -- --- | - | -- | -- | 
 | --- | -- | -- | --
  --  1  | Name of the 1. work place | 1983-07-11 | 1984-08-31 |    
1 | workman |    1   |    1   |   21
  --  2  | Name of the 2. work place | 1984-11-01 | 1986-01-15 |    
1 | workman |    1   |    2   |   15
  --  3  | Name of the 3. work place | 1986-01-16 | 1999-07-16 |    
1 | workman |   13   |    6   |    1
  --  4  | Name of the 4. work place | 2000-02-01 | 2000-08-31 |    
1 | teacher |    0   |    7   |    0
  --  5  | Name of the 4. work place | 2000-09-01 | 2001-01-31 |  
0.5 | teacher |    0   |    5   |    0
  --  6  | Name of the 4. work place | 2001-02-01 | 2018-08-26 |    
1 | teacher |   17   |    6   |   26
  --  7  | Johnny    | 2018-05-01 | 2019-04-30 |    
1 | workman |    0   |   11   |   30


which will show you what you had already asked for - i.e. the length of 
service of all the people. You could also make this into a view so you 
only need to query 1 single item. (If that's a requirement)


Now, once that is done, here are two more views that will give you more 

Re: [sqlite] Why trigger (UPDATE) does not work?

[Jean-Luc Hainaut]

Please check the syntax of the case-end function. As you have written 
them, they just return boolean values.


J-L Hainaut


On 26/08/2018 14:16, Csányi Pál wrote:

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 (
 idINT 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 

Re: [sqlite] Why trigger (UPDATE) does not work?

[Clemens Ladisch]

Csányi Pál wrote:
> CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON 
> MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = (  with ...

 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

Re: [sqlite] Why trigger (UPDATE) does not work?

[Csányi Pál]

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 (
idINT 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) 

Re: [sqlite] Why trigger (UPDATE) does not work?

[Brian Curley]

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.

While it's got a great regex implementation (which doesn't port to running
in pure CLI-based scripts) and a no-nonsense GUI, it also tends to flake
out with memory issues on relatively small databases. I also cannot find if
it's actively supported; no recent versions or activity on the forum.

You might need to crosscheck your results within alternates like
SQLiteAdmin or dbVis.

Regards.

Brian P Curley


On Sun, Aug 26, 2018, 3:41 AM Csányi Pál  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
> Üdvözlettel, Csányi Pál tanár
> ___
> 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

[sqlite] Why trigger (UPDATE) does not work?

[Csányi Pál]

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
Üdvözlettel, Csányi Pál tanár
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users