如何创建根据另一个条目更新条目的 SQLite 触发器? [英] How to create an SQLite trigger that updates an entry depending on another entry?
问题描述
我有一列文件夹名称,我有一个部分目录地址.当我添加或更改文件夹名称时 - 我希望附加目录地址...
I have a column of folder names, and I have a partial directory address. When I add or change a folder name - I want the directory address to be appended...
所以...触发器应该只是自动将文件夹名称添加到目录的末尾 - 当我更新文件夹名称时.但是,虽然我已经在 MySQL 中工作了类似的工作 - 由于某种原因我无法让 SQLite 工作
So...the trigger should just, automatically add the folder name to the end of directories - as I update the folder name. But, while I've gotten similar to work in MySQL - for some reason I can't get SQLite to work
`CREATE TRIGGER df_match_a AFTER UPDATE ON user_cats
BEGIN
SET NEW.save_directory = CONCAT(OLD.save_directory,NEW.folder)
END;`
我得到:
near "SET": syntax error:
这是一个可以执行我想要的工作的 MySQL 代码...当我将新信息添加到用户名和 Playlist_ID 中时,它会自动更新 API_Call >>
Here is a working MySQL code that does what I want... It automatically updates API_Call >> when I add new info into Username - and Playlist_ID
CREATE DEFINER=`root`@`localhost` TRIGGER `api_links` BEFORE INSERT ON `spotify follow lists` FOR EACH ROW BEGIN
SET NEW.`API Call` = CONCAT("https://api.spotify.com/v1/users/",NEW.`Username`,"/playlists/",NEW.`Playlist ID);
END
<小时>
这是数据库转储的样子...
This is what the database dump looks like...
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "user_cats" (
`category` TEXT,
`uploader` TEXT,
`folder` TEXT,
`playlists` TEXT,
`playlist_image` TEXT,
`save_directory` TEXT
);
INSERT INTO user_cats VALUES('Comics','ComicsExplained','DC Rebirth','[''New to DC Comics? Start here!'']',NULL,'%USERPROFILE%\Videos\Online Videos\Comics\ComicsExplained\');
INSERT INTO user_cats VALUES('Comics','Comicstorian',NULL,NULL,NULL,NULL);
INSERT INTO user_cats VALUES('Video Games','IGN','Daily Fix','[''Daily Fix'']',NULL,'%USERPROFILE%\Videos\Online Videos\Video Games\IGN\Daily Fix\');
INSERT INTO user_cats VALUES('Comics','Marvel Entertainment','Marvel Top 10','[''Marvel Top 10'']',NULL,'%USERPROFILE%\Videos\Online Videos\Comics\Marvel Entertainment\');
INSERT INTO user_cats VALUES('','ScrewAttack!',NULL,NULL,NULL,NULL);
COMMIT;
推荐答案
感谢您的帮助;我终于想通了!以下是详细信息...
Thanks for the assistance; I've finally figured it out! Here are the details...
因此,MySQL 执行和 SQLite 之间存在一些差异...
So, there are a few differences between the MySQL execution and SQLite...
所以……这个……
SET NEW.save_directory = CONCAT(OLD.save_directory,NEW.folder)
变成...
SET NEW.save_directory = (OLD.save_directory || NEW.folder)
- 我不需要添加 NEW.声明
SET NEW.save_directory = (OLD.save_directory || NEW.folder)
是语法错误,但是...
Is a syntax error, but...
SET save_directory = (OLD.save_directory || NEW.folder)
作品
- 触发器语句的结尾需要一个分号.
SET save_directory = (OLD.save_directory || NEW.folder)结束;
但是,我应该在 New.folder) 后面加一个分号
`SET save_directory = (OLD.save_directory || NEW.folder);
END;`
<小时>
- 我需要一个
WHERE
选项来仅使用新的folder
名称更新save_directory
列
- I needed a
WHERE
option to only update thesave_directory
column with the newfolder
name
有了这个...
SET save_directory = (OLD.save_directory || NEW.folder);结束;
更改一个 folder
名称将更改 all save_directory
行
A change in one folder
name will change all save_directory
rows
但是,添加 WHERE
选项可确保仅更新具有 NEW.folder
名称的行.
But, adding the WHERE
option ensures only the row with the NEW.folder
name is updated.
SET save_directory = (OLD.save_directory || NEW.folder) WHERE folder = NEW.folder;结束
最后...
- 声明本身需要更改...
使用...
SET save_directory = (OLD.save_directory || "\" || NEW.folder) WHERE folder = NEW.folder;
END
我的旧 save_directory 每次都会附加新的.例如,我会从...开始
My old save_directory would get appended with the new - each time. For example, I'd start with...
%USERPROFILE%\Videos\Online Videos\IGN
将文件夹更新为Daily Fix
...
%USERPROFILE%\Videos\Online Videos\Video Games\IGN\Daily Fix
将文件夹更新为质量效应
...
%USERPROFILE%\Videos\Online Videos\Video Games\IGN\Daily Fix\Mass Effect
等等……
但是,如果我使用类别和上传者列...
But, if I used the category and uploader columns...
UPDATE user_cats SET save_directory = ("%USERPROFILE%\Videos\Online Videos\" || category || "\" || uploader || "\" || NEW.folder) WHERE folder = NEW.folder;
END;
终于成功了!:)
另外,当我插入一个新值而不是更新时,我做了第二个语句来处理.所以,最后的 TRIGGER
语句是...
Also, I made a second statement to handle when I insert a new value and not just when I update. So, the final TRIGGER
statement is...
解决方案:
CREATE TRIGGER df_match_a
AFTER UPDATE ON user_cats FOR EACH ROW
BEGIN
UPDATE user_cats SET save_directory = ("%USERPROFILE%\Videos\Online Videos\" || category || "\" || uploader || "\" || NEW.folder) WHERE folder = NEW.folder;
END;
CREATE TRIGGER df_match_b
AFTER INSERT ON user_cats FOR EACH ROW
BEGIN
UPDATE user_cats SET save_directory = ("%USERPROFILE%\Videos\Online Videos\" || category || "\" || uploader || "\" || NEW.folder) WHERE folder = NEW.folder;
END;
这篇关于如何创建根据另一个条目更新条目的 SQLite 触发器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!