CODEWITHSUNDEEP
mysqlsqltriggers
HahaHortness
HahaHortness

Reputation: 1628

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)

Upvotes: 1

Views: 93

Answers (4)

I_miss_Steve_already
I_miss_Steve_already

Reputation: 251

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?

Upvotes: 0

Abhay
Abhay

Reputation: 6645

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

Upvotes: 1

searlea
searlea

Reputation: 8378

Does it help if you wrap a BEGIN...END around things?

CREATE TRIGGER ...
FOR EACH ROW BEGIN
  IF ...
  ....
  END IF;
END

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.

Upvotes: 1

Related Questions