By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601402 fiddles created (47995 in the last week).
create table Texts
( tid integer not null primary key AUTOINCREMENT
, textval varchar(20) not null unique);
✓
hidden batch(es)
create index x1 on texts (textval);
✓
hidden batch(es)
create table T
( x int not null
, tid int not null references texts (tid)
, primary key (x, tid) );
✓
hidden batch(es)
create view v as
select t.x, texts.textval
from t
join texts
on t.tid = texts.tid
;
✓
hidden batch(es)
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;