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
