如何创建根据另一个条目更新条目的 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...

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

所以……这个……

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

变成...

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

1. 我不需要添加 NEW.声明

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

是语法错误，但是...

Is a syntax error, but...

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

作品

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

SET save_directory = (OLD.save_directory || NEW.folder)结束;

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

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

<小时>

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

1. I needed a WHERE option to only update the save_directory column with the new folder 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;结束

最后...

1. 声明本身需要更改...

使用...

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屋！