0

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...

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;`

I'm getting :

near "SET": syntax error:

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;
Stack Johan
  • 379
  • 1
  • 6
  • 23
  • Your decription does not name a "save_directory". Please provde a `.dump` (from SQLite commandline tool) of a suitable toy database, with corresponding sample data. – Yunnosch May 14 '17 at 05:39
  • Your description sounds lilke the new directory should be `C:\Gotham New\Batman`. I am not sure about the `New` part, maybe there is a typo somewhere? Otherwise please explain more. Maybe you want to replace the "New" in the old path with the name of the new folder? – Yunnosch May 14 '17 at 05:44
  • I only know `set` as part of `UPDATE table_name SET column_name = new_value` (usually followed by some `WHERE ...;`) . – Yunnosch May 14 '17 at 05:46
  • @Yunnosch I'm not sure how to do the dump, but I'll type a line of the database for example. – Stack Johan May 14 '17 at 06:11
  • Open the commadline tool on your toy database. Type `.help`, then read. Then type `.dump` an copy paste the result here. Be sure to do that on a suitable toy database of course. You could type all of the schema and all of the helpful content lines, but dumping is much more convenient and much less error-prone. – Yunnosch May 14 '17 at 06:14
  • Hmm, still can't figure that out. Is that something that requires a server like MySQL does? I'm just using DB Browser. I don't know if there is a command line interface. - but I updated the question with a little more info and fixed a typo. All I want to do is add the value of one column to the end of another. i.e. [cool] >> cool kids... (if I change the column, update) ... cool birds, cool dudes... etc. – Stack Johan May 14 '17 at 06:37
  • Look for the sqlite command line tool on the download page of sqlite, look for the keyword "command line tool" under the appropriate platform. https://www.sqlite.org/download.html I recommend using the command line tool to get a different and "unfiltered" view/access to your data. – Yunnosch May 14 '17 at 10:46
  • By the way, are you sure that you appropriately tagged SQLite for your question? It seems that you are quite unaware of SQLite. Not knowing the command line tool AND asking whether it needs a server (the two together, not each of them alone) is a little surprising to me. How do you access your database? What is your platform? Can you identify your database as a file on your storage medium? – Yunnosch May 14 '17 at 10:54
  • No, I'm not sure. I'm using [DB Browser](http://sqlitebrowser.org/) which can create and edit SQLite files - and I'm using Python. So, to access the database (.sqlite file) I either use DB Browser, or I [`import sqlite3`](http://www.sqlitetutorial.net/sqlite-python/sqlite-python-select/). - but, I still have to use SQL queries, and I don't understand why the query works my MySQL database, but not SQLite – Stack Johan May 14 '17 at 12:56
  • I added the `dump` to my question -- apparently the sqlite3 module for Python [can't do dumps](http://stackoverflow.com/a/35289175/1947026). – Stack Johan May 14 '17 at 13:51
  • @Yunnosch I haven't quite solved my problem, but this is correctly tagged as a sqlite issue. I just discovered that SQLite [uses `||` to concat](https://www.techonthenet.com/sqlite/functions/concatenate.php), MySQL uses the `CONCAT()` function... I also discovered another couple of problems with the syntax (one that's SQLite specific) My current issue is I'm having trouble getting the correct combination of NEW. and OLD. values – Stack Johan May 14 '17 at 14:53

2 Answers2

0

I think it's something like

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;

Stamatia Ch
  • 104
  • 1
  • 3
  • 17
  • Unfortunately, that gives me an error -- `near ".": syntax error: ` – Stack Johan May 14 '17 at 13:12
  • I haven't quite figured it out yet, but I've solved all the syntax issues... The first is that SQLite uses `||` instead of `CONCAT()`. The second is SQL requires a semicolon at the end of the trigger statement and after `END` ...so, after `NEW.folder);` <-- semicolon, then `END;` <-- semicolon Also looks like I don't need to use `SET NEW.` - that gives me another syntax error; so, I made it `SET save_directory` and got no errors – Stack Johan May 14 '17 at 15:15
0

Thanks for the assistance; I've finally figured it out! Here are the details...

So, there are a few differences between the MySQL execution and SQLite...

  1. MySQL uses CONCAT(), but SQLite uses ||

So...this...

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

Became...

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

  1. I don't need to add NEW. to the statement

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

Is a syntax error, but...

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

Works

  1. The end of the trigger statement needs a semicolon.

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

But, I should have a semicolon after New.folder)

`SET save_directory = (OLD.save_directory || NEW.folder);
END;`
  1. I needed a WHERE option to only update the save_directory column with the new folder name

With this...

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

A change in one folder name will change all save_directory rows

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; END

And finally...

  1. The statement itself needed a change...

Using...

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

My old save_directory would get appended with the new - each time. For example, I'd start with...

%USERPROFILE%\Videos\Online Videos\IGN

Update the folder to Daily Fix...

%USERPROFILE%\Videos\Online Videos\Video Games\IGN\Daily Fix

Update the folder to Mass Effect...

%USERPROFILE%\Videos\Online Videos\Video Games\IGN\Daily Fix\Mass Effect

And so on...

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;

It finally works! :)

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...

Solution:

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;
Stack Johan
  • 379
  • 1
  • 6
  • 23