Wow, that's some clever use. I tried doing it all in one go using a `vars`
table (name, value, timestamp) and a `dummy` (empty table to enable access
to case syntax) table, made a mess of case...when...then... only to realize
that a then-expr can't contain insert clause. I guess I'll implement this
in DAL itself.
On Wed, Feb 20, 2019 at 9:15 PM David Raymond <david.raym...@tomtom.com>
wrote:
> Well, you _can_ with this specific simplified schema, but it's a little
> sketchy and most likely will not scale. So if at all possible this sort of
> thing should be done from the controlling program rather than by a trigger.
> Also as mentioned, if the a/b/c _attribute fields aren't unique in the
> tables then it will also just result in duplication. So not the most
> useful. But hey, here's my solution for the over simplified version:
>
>
> As you said we don't have variables, so last_insert_rowid() can't get
> saved for each table.
>
> For this specific schema though the lack of "not null" on the foreign keys
> makes it doable, as long as "not null" is the actual expectation and
> nothing in the m2m table stays around with a null.
>
>
> create trigger trg_summary_insert
> instead of insert on summary
> for each row
> begin
>
> insert into Beta (b_attribute) values (new.b_attribute);
>
> insert into m2mAlphaBeta (FK_b_id) select last_insert_rowid();
> --This leaves FK_a_id null.
>
> insert into Alpha (a_attribute) values (new.a_attribute);
>
> update m2mAlphaBeta set FK_a_id = last_insert_rowid() where FK_a_id is
> null;
> --Only the new row inserted above should have a null, so the new row gets
> updated
> --without us needing to know the rowid for the m2m table.
>
> --And since the previous statement was an update,
> --then last_insert_rowid() will keep the value from the Alpha insert.
> insert into Charlie (c_attribute, FK_C_a_id) values (new.c_attribute,
> last_insert_rowid());
>
> end;
>
>
> sqlite> insert into summary values ('A', 1.0, 'One'), ('B', 2.2, 'Two');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
>
> sqlite> select * from Alpha;
> a_id|a_attribute
> 1|A
> 2|B
>
> sqlite> select * from Beta;
> b_id|b_attribute
> 1|1.0
> 2|2.2
>
> sqlite> select * from Charlie;
> c_id|c_attribute|FK_C_a_id
> 1|One|1
> 2|Two|2
>
> sqlite> select * from m2mAlphaBeta;
> _id|FK_a_id|FK_b_id
> 1|1|1
> 2|2|2
>
> --Throw in junk values to make sure it's not just because of coincidence
> that the new rowid's are the same for each table
>
> sqlite> insert into Alpha values (17, 'Seventeen');
>
> sqlite> insert into Beta values (32, 32.0);
>
> sqlite> insert into Charlie values (57, 'Fifty-Seven', null);
>
> --And try it again
> sqlite> insert into summary values ('C', 3.3, 'Three');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
> C|3.3|Three
>
> --However, this just blindly throws in duplicates since there're no unique
> constraints
>
> sqlite> insert into summary values ('C', 3.3, 'Three');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
> C|3.3|Three
> C|3.3|Three
>
> sqlite>
>
>
> Also at the very end here I realize this also requires no triggers on
> inserts for any of the real tables. Otherwize last_insert_rowid() will be
> giving results from the inserts in the recursive triggers, and throw things
> off.
>
> So yes, if possible do it in the controlling program rather than in
> triggers :)
>
>
>
> -----Original Message-----
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Rocky Ji
> Sent: Wednesday, February 20, 2019 6:14 AM
> To: SQLite mailing list
> Subject: [sqlite] What is the recommended way to write to views?
>
> Hi everyone,
>
> I know the simple answer to be `instead of insert ...`. But consider a
> situation where:
>
> Alpha has-many Beta,and
> Beta has-many Alpha
> Alpha has-many Charlie, while
> Charlie has one Alpha
>
> So if my SQL looks like:
> -- PRAGMA fk ON;
> create table Alpha (
> a_id integer primary key autoincrement,
> a_attribute text
> );
>
> create table Beta (
> b_id integer primary key autoincrement,
> b_attribute real
> );
>
> create table Charlie (
> c_id integer primary key autoincrement,
> c_attribute text,
> FK_C_a_id integer,
> constraint abc foreign key (FK_C_a_id) references Alpha (a_id)
> );
>
> create table m2mAlphaBeta (
> _id integer primary key autoincrement,
> FK_a_id integer,
> FK_b_id integer,
> constraint def foreign key (FK_a_id) references Alpha (a_id),
> constraint ghi foreign key (FK_b_id) references Beta (b_id)
> );
>
>
> create view summary
> as
> select
> a.a_attribute,
> b.b_attribute,
> c.c_attribute
> from
> m2mAlphaBeta m
> inner join Alpha a on a.a_id = m.FK_a_id
> inner join Beta b on b.b_id = m.FK_b_id
> inner join Charlie c on c.FK_C_a_id = a.a_id
> ;
>
>
> And assuming all incoming data (say from CSV read) is correct. How do I
> write a record to `summary`?
>
> Like how do I get ROWID of the "just inserted" record in A to insert into C
> properly. I am thinking "should I make transactions, but we don't have
> variables"... and going round-and-round without solution.
>
> Please recommend the canonical way to write to such views.
>
> Good day.
> _______________________________________________
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
