sql-server - 在 MS SQL 触发器中处理多条记录

Question

我第一次在 MSSQL 中使用触发器，一般情况下都是触发器。在阅读并亲自测试后，我现在意识到触发器是按命令触发的，而不是按插入、删除或更新的行触发的。

整个事情是广告系统的一些统计数据。我们的主统计表相当大，并且不以在大多数情况下有意义的方式包含数据。它包含点击、查看等的每个广告一行。作为用户，人们更倾向于查看这一行，因为 X 天有 Y 次点击量和 Z 次查看量等。到目前为止，我们纯粹基于 SQL 查询来完成此操作，从主表获取此类报告，但随着表的增长，执行该查询的时间也会增加。因此，我们选择使用触发器来更新另一个表，从而使这在 SQL 服务器上变得更容易。

我现在的问题是如何处理多个记录。我所做的是创建 2 个存储过程，一个用于处理插入操作，另一个用于处理删除操作。然后，我的插入触发器(编写用于处理单个记录)从插入表中获取数据，并将其发送到存储过程。删除触发器以相同的方式工作，并且(显然？)更新触发器的作用与删除+插入相同。

我现在的问题是如何最好地处理多个记录。我尝试过使用光标，但就我自己能够阅读和看到的情况来看，这表现得非常糟糕。我也考虑过编写一些“检查” - 例如检查命令中是否有多个记录，然后使用光标，否则只需避免这种情况。无论如何，这是我使用光标的解决方案，我想知道是否有更好的方法？

CREATE TRIGGER [dbo].[TR_STAT_INSERT]
   ON  [iqdev].[dbo].[Stat]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Date DATE 
    DECLARE @CampaignId BIGINT
    DECLARE @CampaignName varchar(500)
    DECLARE @AdvertiserId BIGINT
    DECLARE @PublisherId BIGINT
    DECLARE @Unique BIT
    DECLARE @Approved BIT
    DECLARE @PublisherEarning money
    DECLARE @AdvertiserCost money
    DECLARE @Type smallint

    DECLARE InsertCursor CURSOR FOR SELECT Id FROM Inserted
    DECLARE @curId bigint

    OPEN InsertCursor

    FETCH NEXT FROM InsertCursor INTO @curId

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @Date = [Date], @PublisherId = [PublisherCustomerId], @Approved = [Approved], @Unique = [Unique], @Type = [Type], @AdvertiserCost = AdvertiserCost, @PublisherEarning = PublisherEarning
        FROM Inserted
        WHERE Id = @curId

        SELECT @CampaignId = T1.CampaignId, @CampaignName = T2.Name, @AdvertiserId = T2.CustomerId
        FROM Advert AS T1
        INNER JOIN Campaign AS T2 on T1.CampaignId = T2.Id
        WHERE T1.Id = (SELECT AdvertId FROM Inserted WHERE Id = @curId)

        EXEC ProcStatInsertTrigger @Date, @CampaignId, @CampaignName, @AdvertiserId, @PublisherId, @Unique, @Approved, @PublisherEarning, @AdvertiserCost, @Type

        FETCH NEXT FROM InsertCursor INTO @curId
    END

    CLOSE InsertCursor
    DEALLOCATE InsertCursor
END

存储过程相当大且密集，我不认为有一种方法可以避免以某种方式循环插入表的记录(好吧，也许有，但我想也能够阅读代码:p)，所以我不会让你觉得无聊(除非你不这么认为)。那么，有没有更好的方法来做到这一点，如果有，怎么做？

编辑:请求之后，这是存储过程

CREATE PROCEDURE ProcStatInsertTrigger 
    @Date DATE,
    @CampaignId BIGINT,
    @CampaignName varchar(500),
    @AdvertiserId BIGINT,
    @PublisherId BIGINT,
    @Unique BIT,
    @Approved BIT,
    @PublisherEarning money,
    @AdvertiserCost money,
    @Type smallint
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
IF @Approved = 1
        BEGIN
            DECLARE @test bit

            SELECT @test = 1 FROM CachedStats WHERE [Date] = @Date AND CampaignId = @CampaignId AND CustomerId = @PublisherId

            IF @test IS NULL
                BEGIN
                    INSERT INTO CachedStats ([Date], CustomerId, CampaignId, CampaignName) VALUES (@Date, @PublisherId, @CampaignId, @CampaignName)
                END

            SELECT @test = NULL

                    DECLARE @Clicks int
                    DECLARE @TotalAdvertiserCost money
                    DECLARE @TotalPublisherEarning money
                    DECLARE @PublisherCPC money
                    DECLARE @AdvertiserCPC money

                    SELECT @Clicks = Clicks, @TotalAdvertiserCost = AdvertiserCost + @AdvertiserCost, @TotalPublisherEarning = PublisherEarning + @PublisherEarning FROM CachedStats
                    WHERE [Date] = @Date AND CustomerId = @PublisherId AND CampaignId = @CampaignId

                    IF @Type = 0 -- If click add one to the calculation
                        BEGIN
                            SELECT @Clicks = @Clicks + 1
                        END

                    IF @Clicks > 0
                        BEGIN
                            SELECT @PublisherCPC = @TotalPublisherEarning / @Clicks, @AdvertiserCPC = @TotalAdvertiserCost / @Clicks
                        END
                    ELSE
                        BEGIN
                            SELECT @PublisherCPC = 0, @AdvertiserCPC = 0
                        END
            IF @Type = 0
                BEGIN

                    UPDATE CachedStats SET
                        Clicks = @Clicks,
                        UniqueClicks = UniqueClicks + @Unique,
                        PublisherEarning = @TotalPublisherEarning,
                        AdvertiserCost = @TotalAdvertiserCost,
                        PublisherCPC = @PublisherCPC,
                        AdvertiserCPC = @AdvertiserCPC
                    WHERE [Date] = @Date AND CustomerId = @PublisherId AND CampaignId = @CampaignId
                END
            ELSE IF @Type = 1 OR  @Type = 4 -- lead or coreg
                BEGIN
                    UPDATE CachedStats SET
                        Leads = Leads + 1,
                        PublisherEarning = @TotalPublisherEarning,
                        AdvertiserCost = @TotalAdvertiserCost,
                        AdvertiserCPC = @AdvertiserCPC,
                        PublisherCPC = @AdvertiserCPC
                    WHERE [Date] = @Date AND CustomerId = @PublisherId AND CampaignId = @CampaignId
                END
            ELSE IF @Type = 3 -- Isale
                BEGIN
                    UPDATE CachedStats SET
                        Leads = Leads + 1,
                        PublisherEarning = @TotalPublisherEarning,
                        AdvertiserCost = @TotalAdvertiserCost,
                        AdvertiserCPC = @AdvertiserCPC,
                        PublisherCPC = @AdvertiserCPC,
                        AdvertiserOrderValue = @AdvertiserCost,
                        PublisherOrderValue = @PublisherEarning 
                    WHERE [Date] = @Date AND CustomerId = @PublisherId AND CampaignId = @CampaignId                 
                END
           ELSE IF @Type = 2 -- View
                BEGIN
                    UPDATE CachedStats SET
                        [Views] = [Views] + 1,
                        UniqueViews = UniqueViews + @Unique,
                        PublisherEarning = @TotalPublisherEarning,
                        AdvertiserCost = @TotalAdvertiserCost,
                        PublisherCPC = @PublisherCPC,
                        AdvertiserCPC = @AdvertiserCPC
                    WHERE [Date] = @Date AND CustomerId = @PublisherId AND CampaignId = @CampaignId         
                END
        END
END

经过帮助，这是我的最终结果，发布以防其他人遇到类似问题

CREATE TRIGGER [dbo].[TR_STAT_INSERT]
   ON  [iqdev].[dbo].[Stat]
   AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON

    -- insert all missing "CachedStats" rows
    INSERT INTO
        CachedStats ([Date], AdvertId, CustomerId, CampaignId, CampaignName) 
    SELECT DISTINCT
        CONVERT(Date, i.[Date]), i.AdvertId, i.[PublisherCustomerId], c.Id, c.Name
    FROM
        Inserted i
        INNER JOIN Advert AS   a ON a.Id = i.AdvertId
        INNER JOIN Campaign AS c ON c.Id = a.CampaignId
    WHERE
        i.[Approved] = 1
        AND NOT EXISTS (
                SELECT 1 
                FROM CachedStats as t
                WHERE 
                        [Date] = CONVERT(Date, i.[Date])
                        AND CampaignId = c.Id 
                        AND CustomerId = i.[PublisherCustomerId]
                        AND t.AdvertId = i.AdvertId
        )

  -- update all affected records at once
    UPDATE 
        CachedStats
    SET
        Clicks = 
            Clicks + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] = 0
            ),
        UniqueClicks = 
            UniqueClicks + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.[Unique] = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] = 0
            ),
        [Views] = 
            [Views] + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] = 2
            ),
        UniqueViews = 
            UniqueViews + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.[Unique] = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] = 2
            ),
        Leads = 
            Leads + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.[Unique] = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] IN (1,3,4)
            ),
        PublisherEarning =
            CachedStats.PublisherEarning + ISNULL((
                SELECT SUM(PublisherEarning) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId           

            ), 0),
        AdvertiserCost =
            CachedStats.AdvertiserCost + ISNULL((
                SELECT SUM(AdvertiserCost) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
            ), 0),
        PublisherOrderValue =
            PublisherOrderValue + ISNULL((
                SELECT SUM(PublisherEarning) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] = 3              
            ), 0),
        AdvertiserOrderValue =
            AdvertiserOrderValue + ISNULL((
                SELECT SUM(AdvertiserCost) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId           
                AND   s.[Type] = 3
            ), 0),
        PublisherCPC = 
            CASE WHEN (Clicks + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] = 0
            )) > 0 THEN
                (CachedStats.PublisherEarning + ISNULL((
                SELECT SUM(PublisherEarning) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId           
            ), 0)) -- COST ^
                / (
                    Clicks + (
                        SELECT COUNT(*) FROM Inserted s
                        WHERE s.Approved = 1
                        AND   s.PublisherCustomerId = i.PublisherCustomerId
                        AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                        AND   s.AdvertId = i.AdvertId
                        AND   s.[Type] = 0
                    )               
                ) --- Clicks ^
            ELSE
                0
            END,    
        AdvertiserCPC = 
            CASE WHEN (Clicks + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] = 0
            )) > 0 THEN
                (CachedStats.AdvertiserCost + ISNULL((
                SELECT SUM(AdvertiserCost) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                AND   s.AdvertId = i.AdvertId           
            ), 0)) -- COST ^
                / (
                    Clicks + (
                        SELECT COUNT(*) FROM Inserted s
                        WHERE s.Approved = 1
                        AND   s.PublisherCustomerId = i.PublisherCustomerId
                        AND   CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date])
                        AND   s.AdvertId = i.AdvertId
                        AND   s.[Type] = 0
                    )               
                ) --- Clicks ^
            ELSE
                0
            END     
   FROM
        Inserted i
    WHERE
        i.Approved = 1 AND
        CachedStats.Advertid = i.AdvertId AND
        CachedStats.[Date] = Convert(Date, i.[Date]) AND
        CachedStats.CustomerId = i.PublisherCustomerId
  SET NOCOUNT OFF
END

现在看起来略有不同，因为我也必须为每个广告建立索引 - 但非常感谢您的帮助 - 将一切速度从 30 小时以上加快到 30 秒，从我自己的开发统计表生成 CachedStats :)

Answer 1

处理这些情况的技巧是将顺序操作(对于每个记录执行xyz)转换为基于集合的操作(UPDATE 语句)。

我分析了您的存储过程并将您单独的 UPDATE 语句合并为一个。然后，可以将该单个语句转换为可立即应用于所有插入记录的版本，从而消除对存储过程的需要，从而消除对游标的需要。

编辑:下面是我们最终开始工作的代码。根据OP的反馈，整个操作的执行时间从“几乎永远”(对于原始解决方案)减少到不到一秒。总体代码大小也显着减少。

CREATE TRIGGER [dbo].[TR_STAT_INSERT]
   ON  [iqdev].[dbo].[Stat]
   AFTER INSERT
AS 
BEGIN
  SET NOCOUNT ON

  -- insert all missing "CachedStats" rows
  INSERT INTO
    CachedStats ([Date], AdvertId, CustomerId, CampaignId, CampaignName) 
  SELECT DISTINCT
    CONVERT(Date, i.[Date]), i.AdvertId, i.PublisherCustomerId, c.Id, c.Name
  FROM
    Inserted i
    INNER JOIN Advert   a ON a.Id = i.AdvertId
    INNER JOIN Campaign c ON c.Id = a.CampaignId
  WHERE
    i.Approved = 1
    AND NOT EXISTS ( 
      SELECT 1 
      FROM   CachedStats
      WHERE  Advertid   = i.AdvertId AND
             CustomerId = i.PublisherCustomerId AND
             [Date]     = CONVERT(DATE, i.[Date])
    )

  -- update all affected records at once
  UPDATE 
    CachedStats
  SET
    Clicks               = Clicks               + i.AddedClicks,
    UniqueClicks         = UniqueClicks         + i.AddedUniqueClicks,
    [Views]              = [Views]              + i.AddedViews,
    UniqueViews          = UniqueViews          + i.AddedUniqueViews,
    Leads                = Leads                + i.AddedLeads,
    PublisherEarning     = PublisherEarning     + ISNULL(i.AddedPublisherEarning, 0),
    AdvertiserCost       = AdvertiserCost       + ISNULL(i.AddedAdvertiserCost, 0),
    PublisherOrderValue  = PublisherOrderValue  + ISNULL(i.AddedPublisherOrderValue, 0),
    AdvertiserOrderValue = AdvertiserOrderValue + ISNULL(i.AddedAdvertiserOrderValue, 0)
  FROM
    (
    SELECT
      AdvertId,
      CONVERT(DATE, [Date]) [Date],
      PublisherCustomerId,
      COUNT(*) NumRows,
      SUM(CASE WHEN Type IN (0)                      THEN 1 ELSE 0 END) AddedClicks,
      SUM(CASE WHEN Type IN (0)     AND [Unique] = 1 THEN 1 ELSE 0 END) AddedUniqueClicks,
      SUM(CASE WHEN Type IN (2)                      THEN 1 ELSE 0 END) AddedViews,
      SUM(CASE WHEN Type IN (2)     AND [Unique] = 1 THEN 1 ELSE 0 END) AddedUniqueViews,
      SUM(CASE WHEN Type IN (1,3,4) AND [Unique] = 1 THEN 1 ELSE 0 END) AddedLeads,
      SUM(PublisherEarning)                                      AddedPublisherEarning,
      SUM(AdvertiserCost)                                        AddedAdvertiserCost,
      SUM(CASE WHEN Type IN (3) THEN PublisherOrderValue  ELSE 0 END) AddedPublisherOrderValue,
      SUM(CASE WHEN Type IN (3) THEN AdvertiserOrderValue ELSE 0 END) AddedAdvertiserOrderValue
    FROM
      Inserted
    WHERE
      Approved = 1
    GROUP BY
      AdvertId,
      CONVERT(DATE, [Date]),
      PublisherCustomerId
    ) i 
    INNER JOIN CachedStats cs ON 
      cs.Advertid   = i.AdvertId AND
      cs.CustomerId = i.PublisherCustomerId AND
      cs.[Date]     = i.[Date]

  SET NOCOUNT OFF
END

涉及 CachedStats 表的操作将大大受益于 (Advertid, CustomerId, [Date]) 的多列索引(正如 OP 所确认的那样)。