create table Texts ( tid integer not null primary key AUTOINCREMENT , textval varchar(20) not null unique);

create index x1 on texts (textval);

create table T ( x int not null , tid int not null references texts (tid) , primary key (x, tid) );

create view v as select t.x, texts.textval from t join texts on t.tid = texts.tid ;

CREATE TRIGGER trig1 INSTEAD OF INSERT ON V BEGIN -- insert unless textval is already in place INSERT INTO texts (textval) SELECT NEW.textval FROM (VALUES(1)) WHERE NOT EXISTS ( SELECT 1 FROM texts WHERE textval = NEW.textval ); --lookup tid for textval insert into t (x, tid) select NEW.x, texts.tid from texts where texts.textval = NEW.textval; END;

insert into V (x,textval) values (1,'a');

insert into V (x,textval) values (5,'bb');

insert into V (x,textval) values (15,'a');

select * from v;

select * from texts;

select * from t;