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