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
