Mysql syntax error when creating trigger on delete

Question

I am having an issue creating a trigger in my database. The idea is that, when a song is removed from a playlist, the corresponding row is deleted from the table playlistcancion. The idea of the trigger is that it removes the time of the deleted song from the total time of the playlist.

view name: pycs (shows songs of corresponding playlists)
column name | SongID | playlistID | Playlist | songName | Duration
data type   | INT    | INT        | varchar  | varchar  | TIME 

table name: playlistcancion (keeps track of which songs are in which playlists)
column name | songID | playlistID 
data type   | INT    | INT   

table name: playlists (playlist data and creators userID)
column name | playlistID| userID  | Followers  | Title  | TotalDuration
data type   | INT       | INT     | INT        | varchar| TIME

Here is my trigger query:

CREATE TRIGGER remove_time
AFTER DELETE ON playlistcancion
FOR EACH ROW 
    DECLARE dur TIME;
    SELECT Duration INTO dur FROM pycs WHERE playlistID = OLD.playlistID AND songID = OLD.songID;
    UPDATE playlists SET TotalDuration = SEC_TO_TIME(TIME_TO_SEC(TotalDuration) - TIME_TO_SEC(dur))
    WHERE playlistID = OLD.playlistID;

The error i am getting is: MySQL said: #1064 - There's something wrong with your syntax near 'DECLARE dur TIME; SELECT Duration INTO dur FROM pycs WHERE playlisID = OLD.pla' in line 1 I am using phpMyAdmin to create the trigger. Thanks in advance.

Wasim Ansari · Accepted Answer · 2020-07-31 06:24:06Z

1

Adding BEGIN and END resolved that error you are getting.

CREATE TRIGGER remove_time
AFTER DELETE ON playlistcancion
FOR EACH ROW BEGIN
    DECLARE dur TIME;
    SELECT Duration INTO dur FROM pycs WHERE playlistID = OLD.playlistID AND songID = OLD.songID;
    UPDATE playlists SET TotalDuration = SEC_TO_TIME(TIME_TO_SEC(TotalDuration) - TIME_TO_SEC(dur))
    WHERE playlistID = OLD.playlistID;
    END;

If you face more issue better post with insert script of all the tables involved in the triggers.

answered Jul 31, 2020 at 6:24

Wasim Ansari

2901 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Akina · Accepted Answer · 2020-07-31 06:46:13Z

1

Combine your trigger into single-statement form:

CREATE TRIGGER remove_time
AFTER DELETE 
ON playlistcancion
FOR EACH ROW 
UPDATE playlists 
SET TotalDuration = SEC_TO_TIME(   TIME_TO_SEC(TotalDuration) 
                                 - TIME_TO_SEC(SELECT Duration 
                                               FROM pycs 
                                               WHERE playlistID = OLD.playlistID 
                                                 AND songID = OLD.songID))
WHERE playlistID = OLD.playlistID;

Now it does not need in intermediate variable, BEGIN-END block and DELIMITER reassign.

answered Jul 31, 2020 at 6:46

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Mysql syntax error when creating trigger on delete

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related