RSQLite中tracks表AFTER INSERT触发器语法错误求助

问题背景 #

我正在使用SQL练习常用的Media.db完成作业：已为albums表添加play_time列、设置正数值约束，编写了更新该列以计算专辑总播放时长的语句，但在为tracks表创建AFTER INSERT触发器以自动更新play_time时，出现语法错误（Error: near "TrackId": syntax error）。以下是我的代码及问题修复方案：

原step_3更新语句问题分析 #

# update the "play_time" column 
step_3 <- paste0(" UPDATE albums 
                  SET play_time = ( SELECT 
                    tracks.albumid
                    title
                    COUNT(play_time)
                  FROM
                      tracks
                  INNER JOIN albums ON albums.albumid = tracks.albumid
                  GROUP BY 
                      tracks.albumid)
                  WHERE
                      play_time
                  
")

rs3 <- dbExecute(dbcon,step_3)

存在的问题：

• 子查询返回3列数据，但SET play_time仅能接收单个数值，逻辑冲突
• 计算总播放时长需用SUM(play_time)而非COUNT(play_time)，COUNT是统计行数，SUM才是对时长求和
• WHERE子句无具体匹配条件，会导致更新逻辑混乱
• 子查询未关联外层albums表，无法对应到每一行的专辑

修正后的step_3代码 #

# update the "play_time" column 
step_3 <- paste0(" UPDATE albums 
                  SET play_time = ( 
                    SELECT SUM(t.play_time)
                    FROM tracks t
                    WHERE t.AlbumId = albums.AlbumId
                  )
                  WHERE EXISTS (
                    SELECT 1 FROM tracks t WHERE t.AlbumId = albums.AlbumId
                  );
")

rs3 <- dbExecute(dbcon,step_3)

原step_4触发器语法错误分析 #

# add the trigger
step_4 <- paste0(" CREATE TRIGGER IF NOT EXISTS
                  AFTER INSERT  TrackId ON tracks
                 FOR EACH ROW
                 WHEN
                 BEGIN
                    (UPDATE albums 
                  SET play_time = ( SELECT 
                      COUNT(play_time)
                  FROM
                      tracks
                  INNER JOIN albums ON albums.albumid = tracks.albumid
                  GROUP BY 
                      tracks.albumid))
                 ")
rs4 <- dbExecute(dbcon,step_4)

存在的问题：

• 触发器语法错误：AFTER INSERT后不应添加TrackId，正确格式为AFTER INSERT ON tracks
• WHEN子句未指定任何条件，属于无效语法
• 更新逻辑冗余：插入新曲目时无需全表重新计算，仅需更新新曲目所属专辑的play_time即可
• 缺少触发器结束标记END
• 误用COUNT(play_time)而非SUM(play_time)计算总时长

修正后的step_4代码 #

# add the trigger
step_4 <- paste0(" CREATE TRIGGER IF NOT EXISTS update_album_playtime
                  AFTER INSERT ON tracks
                 FOR EACH ROW
                 BEGIN
                    UPDATE albums 
                    SET play_time = ( 
                      SELECT SUM(t.play_time)
                      FROM tracks t
                      WHERE t.AlbumId = NEW.AlbumId
                    )
                    WHERE AlbumId = NEW.AlbumId;
                 END;
")

rs4 <- dbExecute(dbcon,step_4)

内容的提问来源于stack exchange，提问作者Muzhdah-W