It just occurred to me that I didn't completely finish my thought in the
earlier reply.
The suggested pivot views synchronization TRIGGER is named
quarterly_sales_fruit_insert instead of simply quarterly_sales_insert for a
specific reason. There is no need to regenerate pivot views unless the
fruit set changes.
So, the more complete solution is to use the implied monotonic rowid column
to condition view regeneration if and only if a new fruit type was added to
the quarterly_sales table. See the WHEN clause below for how that works.
[Also, obviously, similar UPDATE and DELETE triggers will also needed if
the sales table must accommodate changes to existing records.]
DROP TRIGGER IF EXISTS quarterly_sales_fruit_insert;
CREATE TRIGGER quarterly_sales_fruit_insert AFTER INSERT ON quarterly_sales
WHEN new.fruit NOT IN (SELECT DISTINCT fruit from quarterly_sales WHERE
rowid<new.rowid)
BEGIN
INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','sum');
INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','min');
INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','max');
INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','avg');
END;
On Tue, May 16, 2017 at 4:32 PM, E.Pasma <pasm...@concepts.nl> wrote:
> 15 mei 2017, 07:34 petern:
>
> Here I revisit the pivot table problem using the SQLite stored procedure
>> pattern and pure unmodified SQLite. I am seeking feedback for improving
>> the brevity of the stored procedure presented here.
>>
>
> Hi, initially I got: near "eval": syntax error. This is after saving the
> mail message as a text file and taking that in the editor two become an sql
> script. This apparently leaves some two-byte spaces, causing this humanly
> invisable error.
> How to fix this in VI I don't know.
> Downloading the text by simple copy and paste cures fixed the problem.
> The procedure works great and can easily be customized.
> I'd generate a temporary view as one must not be tempted to keep it (and
> miss new fruits).
> Thanks, E. Pasma
>
> _______________________________________________
> 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
