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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
