Your create view had 2 typos in it by the way. Here's a fixed version for those copying and pasting:
CREATE VIEW select_file_dirs AS SELECT dir_paths.dir_path AS dir_path, files.f_name FROM dir_paths INNER JOIN files ON files.dir_id = dir_paths.id; I think you're also going to want a unique constraint on files (dir_id, f_name) to avoid duplicate file names What you're looking for is this I believe: create trigger trg_select_file_dirs_insert instead of insert on select_file_dirs begin insert into dir_paths (dir_path) values (new.dir_path) on conflict (dir_path) do nothing; insert into files (f_name, dir_id) values (new.f_name, (select id from dir_paths where dir_path = new.dir_path)); end; -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of InAsset.Michele Petrazzo Sent: Thursday, August 22, 2019 6:30 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] conditional trigger Hi all, I'm trying to create a conditional trigger for add some data to my sqlite db only when it isn't inside (yet). My tables: CREATE TABLE dir_paths ( id integer PRIMARY KEY, dir_path TEXT NOT NULL UNIQUE ); CREATE TABLE files ( id INTEGER PRIMARY KEY, f_name TEXT NOT NULL, dir_id integer NOT NULL, FOREIGN KEY (dir_id) REFERENCES dir_paths (id) ); My need: I would like to create a trigger (or a view + a trigger associate) that check if dir_path (and of course dir_paths.id) is present and if not, create it into a single INSERT transition. If yes, SELECT the dir_path.id and pass it to the INSERT into files statement. On the other hand, I have already a function that select the id of dir_paths if present, and if not insert into it. But for performances reasons, I need a single transition. I tried with view+trigger: CREATE VIEW select_file_dirs AS SELECT dir_paths.dir_path AS dir_path, files.f_name, FROM dir_paths INNER JOIN files ON files.dir_id = dir_path.id; + CREATE TRIGGER check_dir_present INSTEAD OF INSERT ON select_file_dirs BEGIN CASE WHEN ((SELECT id FROM dir_paths WHERE id = NEW.dir_id ) ISNULL) THEN INSERT INTO dir_paths (directory) VALUES (NEW.dir_path) END but I receive: Error: near "CASE": syntax error on the other side, I create a trigger before the insert into files, but In the "INSERT INTO files" I haven't the "dir_path" info... CREATE TRIGGER check_dir_present BEFORE INSERT ON files WHEN ((SELECT id FROM dir_paths WHERE id = NEW.dir_id ) ISNULL) BEGIN INSERT INTO dir_paths (dir_path) VALUES (NEW.dir_path); END; Some may help me? Thanks _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users