插入Advertisement时触发Subquery多值错误排查求助

首先，你遇到的Subquery returned more than 1 value错误，根源在于触发器里的两个子查询在特定场景下返回了多行结果，但你用了=进行比较（或作为表达式使用）——这在SQL Server里是不允许的。咱们一步步拆解问题并给出修复方案：

1. 第一个问题点：ProductType筛选的子查询 #

看触发器里这段条件：

where pr.ProductType = (select ProductType from Product join Advertisement on Product.IDProduct = Advertisement.IDProduct join inserted on Advertisement.IDProduct = inserted.IDProduct where Advertisement.IDAdvertisement = inserted.IDAdvertisement)

这里有两个隐患：

• 当inserted表包含多行数据时（哪怕你现在是单条插入，触发器也要兼容批量插入的场景），这个子查询会返回多个ProductType值，用=直接比较就会触发报错。
• 关联逻辑冗余，其实可以直接通过inserted关联到Product表获取ProductType，不需要绕Advertisement表。

2. 第二个问题点：获取IDAdvertisement的子查询 #

这段代码里的子查询：

(select IDAdvertisement from Advertisement where IDProduct = @IDProduct)

如果同一个IDProduct下存在多条Advertisement记录，这个子查询就会返回多行结果，而你把它作为insert into UserAdvertisementScore的字段值，自然会触发错误。更关键的是：你要的是刚插入的那条广告的IDAdvertisement，直接从inserted表取就可以了，完全不需要再去查Advertisement表！

优化后的触发器代码 #

另外，你用游标循环处理的写法效率很低，建议改成集合操作的方式，同时解决上述两个问题：

ALTER trigger [dbo].[AutomaticUserAdvertisementScoreCalculating] 
ON [dbo].[Advertisement] 
AFTER INSERT 
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回影响行数的消息，干扰应用程序逻辑

    -- 用集合操作批量插入，替代低效的游标循环
    INSERT INTO UserAdvertisementScore (IDUser, IDAdvertisement, Score)
    SELECT 
        u.IDUser,
        i.IDAdvertisement, -- 直接从inserted取刚插入的广告ID
        SUM(pp.Price) AS TotalPrice
    FROM "User" u
    JOIN Purchase pu ON u.IDUser = pu.IDUser
    JOIN PurchaseProduct pp ON pu.IDPurchase = pp.IDPurchase
    JOIN Product pr ON pp.IDProduct = pr.IDProduct
    JOIN inserted i ON pr.IDProduct = i.IDProduct
    -- 直接关联inserted到Product获取对应ProductType，避免子查询多行问题
    WHERE pr.ProductType = (SELECT ProductType FROM Product WHERE IDProduct = i.IDProduct)
    GROUP BY u.IDUser, i.IDAdvertisement
    HAVING SUM(pp.Price) > 50;
END

额外的优化建议 #

• 触发器里不需要手动加begin transaction和commit，因为触发器本身是在触发它的语句的事务上下文里执行的，手动加事务可能导致嵌套事务的问题。
• 给你的插入SQL明确指定列名（虽然当前顺序是对的，但明确列名更安全，也更易维护）：

  public static String SQL_INSERT = "INSERT INTO \"Advertisement\" (IDProduct, CampaignDescription, CampaignStart, CampaignEnd) VALUES (@IDProduct, @CampaignDescription, @CampaignStart, @CampaignEnd)";
  

这样修改后，应该就能解决你遇到的子查询返回多行的问题，同时触发器的执行效率也会大幅提升。

内容的提问来源于stack exchange，提问作者Tomato