Thanks for the suggestion on `UNIQUE`, I should have done it. Based on this
new schema I wrote (pseudoSQL code):
create table dummy (d text); -- empty table to let us use
case...when...then syntax
create table variables(
variable_id integer primary key autoincrement,
name text not null,
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
The insert sequence does not work in all cases. This fixes one particular
problem of duplicate or changed linkage to parent in Alpha:Charlie ...
create trigger trg_summary_insert
instead of insert on summary
for each row
begin
insert or ignore into Alpha (a_attribute) values
Or as a trigger of course:
create table Alpha
(
id integer primary key,
a_attribute text not null collate nocase unique
);
create table Beta
(
id integer primary key,
b_attribute text not null collate nocase unique
);
create table Charlie
(
id integer primary
Your constraints are logically inconsistent and incompletely specified.
>Alpha has-many Beta,and
>Beta has-many Alpha
>Alpha has-many Charlie, while
>Charlie has one Alpha
Implies that:
Alpha:Beta is N:M and Beta:Alpha is N:M. Fine.
but you did not say whether the instant relationships are
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
On 20 Feb 2019, at 11:14am, Rocky Ji wrote:
> 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
>
Hi,
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.
Is last_insert_rowid what you're looking for?
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
9 matches
Mail list logo
