Why trigger (UPDATE) does not work?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Why trigger (UPDATE) does not work?

csanyipal
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why trigger (UPDATE) does not work?

csanyipal
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: 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 ...

<https://www.sqlite.org/lang_with.html> says:
| The WITH clause cannot be used within a CREATE TRIGGER.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why trigger (UPDATE) does not work?

Jean-Luc Hainaut
In reply to this post by csanyipal

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 (
>      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 <[hidden email]> 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


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why trigger (UPDATE) does not work?

R Smith-2
In reply to this post by csanyipal
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
information - if it is ever needed and if you plan ahead, i.e. adding
service EndDate that are not yet reached.
The first view below will show the Service time Elapsed (i.e. how long
have they been in service until today).
The next one will show the Service time remaining (i.e. how long do they
still work until the end of their service - if their work did not end
yet, else showing Zero).
Then at the end, there is a last view which adds all this together into
one single view that you can query with all information available and a
sample query to call it.


CREATE VIEW MyLengthOfServiceElapsed AS
WITH worktime(id, dStart, dEnd) AS (
   SELECT id, date(StartDate), CASE WHEN date('now') < date(EndDate)
THEN date('now') ELSE date(EndDate) END 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 ElapsedYears, post.months AS
ElapsedMonths, post.days AS ElapsedDays
   FROM post
;


CREATE VIEW MyLengthOfServiceRemain AS
WITH worktime(id, dStart, dEnd) AS (
   SELECT id, CASE WHEN date('now') BETWEEN date(StartDate) AND
date(EndDate) THEN date('now') ELSE date(EndDate) END, 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 END
     FROM timefix
)
SELECT post.id, post.years AS RemainYears, post.months AS RemainMonths,
post.days AS RemainDays
   FROM post
;


CREATE VIEW MyLengthOfServiceAll AS
SELECT A.*,
        B.TotalYears, B.TotalMonths, B.TotalDays,
        C.ElapsedYears, C.ElapsedMonths, C.ElapsedDays,
        D.RemainYears, D.RemainMonths, D.RemainDays
   FROM MyLengthOfService AS A
   JOIN MyLengthOfServiceTotal AS B ON B.id = A.id
   JOIN MyLengthOfServiceElapsed  AS C ON C.id = A.id
   JOIN MyLengthOfServiceRemain AS D ON D.id = A.id
;


This Query will then show all:

SELECT * FROM MyLengthOfServiceAll;


This is all working well on sqlite 3.24.0 on my side, but I may have
copy-pasted something wrong - if you have any questions or difficulties,
please ask again.

Hope that helps,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why trigger (UPDATE) does not work?

Simon Slavin-3
On 26 Aug 2018, at 6:17pm, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why trigger (UPDATE) does not work?

csanyipal
In reply to this post by R Smith-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Why trigger (UPDATE) does not work?

R Smith-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why trigger (UPDATE) does not work?

Keith Medcalf
In reply to this post by Clemens Ladisch

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users