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:[email protected]] 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
