sql服务器-在sql中创建CTE与选择条件

Question

我正在进行一个学校数据库项目，该项目需要一个基于触发器的解决方案来解决我的数据库中的一些可选限制。我的数据库模型代表了一种基于在线的视频观看服务，用户可以访问大量视频（原理与YouTube类似）。表历史记录为每个用户存储多达100个已观看的视频。我的触发器应该做的是：

• 删除同一视频和用户以前的条目（或将观看日期更改为当前时间）
• 插入新条目（或更新现有条目，即可能的步骤1）
• 删除任何超过第100个的条目

这是我写的代码：

 CREATE TRIGGER [History_Update] ON dbo.History INSTEAD OF INSERT AS
BEGIN
DECLARE @user_id bigint, @video_id bigint, @history_count smallint 
SELECT @user_id=user_id, @video_id=video_id FROM inserted
DELETE FROM History where user_id = @user_id AND video_id=@video_id
INSERT INTO History(user_id, video_id) VALUES (@user_id, @video_id)
SET @history_count = (select count(*) FROM History WHERE user_id= @user_id AND video_id = @video_id)
IF( @history_count >= 100)
BEGIN
    WITH temp AS (SELECT TOP 1 * FROM History WHERE user_id= @user_id AND video_id=@video_id ORDER BY viewtime ASC) 
    DELETE FROM temp
END
END

现在，我对此有几个问题：

• 是像上面写的那样使用CTE更好还是类似这样的东西：

  SET @viewtime = (SELECT TOP 1 viewtime FROM History WHERE user_id= @user_id AND video_id=@video_id ORDER BY viewtime ASC) 
  DELETE FROM History where user_id = @user_id AND video_id=@video_id AND viewtime = @viewtime
  

• 此外，最好检查历史记录中是否存在特定的用户视频条目，然后更新viewtime属性。此外，由于我使用的是INSTEAD OF触发器，这会违反任何关于使用这种触发器的规则吗？因为我不确定我是否理解它。根据我在网上读到的内容，INSTEAD OF触发器必须在触发器的主体中执行指定的操作。谢谢

Answer 1

如果在表达式和set之间进行选择，我会选择CTE。我发现将set与子查询一起使用有点恶心。毕竟，以下操作是相同的：

SELECT TOP 1 @viewtime = viewtime
FROM History
WHERE user_id = @user_id AND video_id = @video_id
ORDER BY viewtime ASC; 

换句话说，set是冗余的。

此外，将set与delete分离引入了竞争条件的机会。也许另一个查询可能会插入一行或删除您试图删除的行。

至于CTE本身，它是可以的。如果要按特定顺序删除行，则需要CTE（或子查询）。