如何创建一个 SQLite 触发器，根据另一个条目更新一个条目?

Question

我有一列文件夹名称，我有一个部分目录地址。当我添加或更改文件夹名称时 - 我希望附加目录地址...

所以。。。触发器应该只是自动将文件夹名称添加到目录的末尾 - 当我更新文件夹名称时。但是，虽然我已经与MySQL的工作类似 - 由于某种原因，我无法让SQLite工作

`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代码，可以做我想要的... 当我将新信息添加到用户名时，它会自动更新API_Call>> - 并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

---

这就是数据库转储的样子...

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%VideosOnline VideosComicsComicsExplained');
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%VideosOnline VideosVideo GamesIGNDaily Fix');
INSERT INTO user_cats VALUES('Comics','Marvel Entertainment','Marvel Top 10','[''Marvel Top 10'']',NULL,'%USERPROFILE%VideosOnline VideosComicsMarvel Entertainment');
INSERT INTO user_cats VALUES('','ScrewAttack!',NULL,NULL,NULL,NULL);
COMMIT;

Answer 1

我认为它是这样的

CREATE TRIGGER df_match_a AFTER UPDATE ON user_cats BEGIN UPDATE user_cats SET NEW.save_directory = CONCAT(OLD.save_directory,NEW.folder) END;

Answer 2

感谢您的帮助;我终于想通了！ 以下是详细信息...

因此，MySQL执行和SQLite之间存在一些差异...

1. MySQL使用CONCAT()，但SQLite使用||

所以。。。这。。。

SET NEW.save_directory = CONCAT(OLD.save_directory,NEW.folder)

成为。。。

SET NEW.save_directory = (OLD.save_directory || NEW.folder)

---

2. 我不需要添加 NEW。 到语句

SET NEW.save_directory = (OLD.save_directory || NEW.folder)

是语法错误，但是...

SET save_directory = (OLD.save_directory || NEW.folder)

工程

---

3. 触发器语句的结尾需要一个分号。

SET save_directory = (OLD.save_directory || NEW.folder) END;

但是，我应该在New.folder)之后有一个分号

`SET save_directory = (OLD.save_directory || NEW.folder);
END;`

---

4. 我需要一个WHERE选项来仅使用新的folder名称更新save_directory列

有了这个...

SET save_directory = (OLD.save_directory || NEW.folder); END;

更改一个folder名称将更改所有save_directory行

但是，添加WHERE选项可确保仅更新具有NEW.folder名称的行。

SET save_directory = (OLD.save_directory || NEW.folder) WHERE folder = NEW.folder; END

---

最后...

5. 语句本身需要更改...

用。。。

SET save_directory = (OLD.save_directory || "" || NEW.folder) WHERE folder = NEW.folder;
END

我的旧save_directory每次都会附加新的。 例如，我会从...

%USERPROFILE%VideosOnline VideosIGN

将文件夹更新为Daily Fix...

%USERPROFILE%VideosOnline VideosVideo GamesIGNDaily Fix

将文件夹更新为Mass Effect...

%USERPROFILE%VideosOnline VideosVideo GamesIGNDaily FixMass Effect

等等...

但是，如果我使用类别和上传者列...

UPDATE user_cats SET save_directory = ("%USERPROFILE%VideosOnline Videos" || category || "" || uploader || "" || NEW.folder) WHERE folder = NEW.folder;
END;

终于成功了！:)

此外，我在插入新值时做了第二个语句来处理，而不仅仅是在更新时处理。所以，最后TRIGGER声明是...

---

溶液：

CREATE TRIGGER df_match_a
AFTER UPDATE ON user_cats FOR EACH ROW
BEGIN
UPDATE user_cats SET save_directory = ("%USERPROFILE%VideosOnline 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%VideosOnline Videos" || category || "" || uploader || "" || NEW.folder) WHERE folder = NEW.folder;
END;