Mind writing my sql statement for me?
This kind of sad but I've been at it a while and I just can't seem to figure this statement out, google searches turn up similar questions but I haven't successfully applied the solutions.
I have a table of music, and every time I insert a song into it(each row is a song) I want to insert the song into a table of clean music if it is flagged as clean. I'm using mysql.
use music;
CREATE TRIGGER cache_clean_music BEFORE INSERT ON music
FOR EACH ROW
if new.clean then
insert into clean_music values (new.artist, new.album, new.song, new.filename, new.clean);
end if;
The Error I get is
ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
and here is a description of the music table, the clean_music table is exactly the same
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra 开发者_如何学编程 |
+----------+---------------------+------+-----+---------+----------------+
| artist | varchar(100) | YES | | NULL | |
| album | varchar(100) | YES | | NULL | |
| song | varchar(100) | YES | | NULL | |
| filename | varchar(100) | YES | | NULL | |
| clean | tinyint(1) | YES | | NULL | |
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
+----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
If the two tables are identical (or almost), you probably do not need triggers (and all their mess) at all.
You may use a VIEW
instead of a table (with duplicate data) for cache_clean_music
:
CREATE VIEW cache_clean_music AS
SELECT artist
, album
, song
, filename ---- and possibly other fields you need
, id
FROM music
WHERE clean ;
Adding an index on music.clean
would be a good idea in this case.
Does it help if you wrap a BEGIN...END around things?
CREATE TRIGGER ...
FOR EACH ROW BEGIN
IF ...
....
END IF;
END
The error - ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 - means that the values in some of your input params isn't correct, perhaps there is some mismatching single quote. Can you display your query or the value in the NEW. variables?
Also, once you have fixed that error, your query will also return another error that "the column count doesn't match value count". And that will be because your table has 6 columns but your INSERT has only 5. Mention the columns in your INSERT query and it should be fine, like:
insert into clean_music (artist, album, song, filename, clean) values (new.artist, new.album, new.song, new.filename, new.clean);
Your clue is this " check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4"
Do you know what version of mysql server you are running?
Did you check the manual to make sure that the command you have written is allowed in that version?
精彩评论