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
