在delete上创建触发器时出现Mysql语法错误
在delete上创建触发器时出现Mysql语法错误
提问于 2020-07-31 14:19:35
我在数据库中创建触发器时遇到问题。其思想是，当从播放列表中移除歌曲时，从表playlistcancion中删除对应的行。触发器的思想是它从播放列表的总时间中移除被删除的歌曲的时间。
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 下面是我的触发器查询：
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;我得到的错误是: MySQL说：#1064 -在第1行的'DECLARE dur TIME；SELECT dur dur FROM pycs WHERE playlisID = OLD.pla‘附近有一些语法错误我正在使用phpMyAdmin创建触发器。提前谢谢。
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-07-31 14:24:07
添加BEGIN和END解决了您得到的错误。
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;如果你面临更多的问题，最好使用触发器中涉及的所有表的插入脚本进行post。
Stack Overflow用户
发布于 2020-07-31 14:46:14
将触发器组合成单语句形式：
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;现在它不需要在中间变量，开始-结束块和分隔符重新赋值。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63186713
复制相关文章
相似问题
腾讯云开发者
