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

2022-01-01 00:00:00 sql-server triggers

我第一次必须在 MSSQL 中使用触发器，一般来说都是触发器.仔细阅读并自己测试后，我现在意识到触发器是按命令触发的，而不是按插入、删除或更新的行触发.

I am having to use triggers in MSSQL for the first time, well triggers in general. Having read around and tested this myself I realise now that a trigger fires per command and not per row inserted, deleted or updated.

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

The entire thing is some statistics for an advertising system. Our main stat table is rather large and doesn't contain the data in a way that makes sense in most cases. It contains one row per advert clicked, viewed and etc. As a user one is more inclined to want to view this as day X has Y amount of clicks and Z amount of views and so forth. We have done this purely based on a SQL query so far, getting this sort of report from the main table, but as the table has grown so does the time for that query to execute. Because of this we have opted for using triggers to keep another table updated and hence making this a bit easier on the SQL server.

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

My issue is now to get this working with multiple records. What I have done is to create 2 stored procedures, one for handling the operation of an insert, and one for a delete. My insert trigger (written to work with a single record) then graps the data off the Inserted table, and sends it off to the stored procedure. The delete trigger works in the same way, and (obviously?) the update trigger does the same as a delete + an insert.

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

My issue is now how to best do this with multiple records. I have tried using a cursor, but as far as I have been able to read and see myself, this performs really badly. I have considered writing some "checks" as well - as in checking to see IF there are multiple records in the commands and then go with the cursor, and otherwise simply just avoid this. Anyhow, here's my solution with a cursor, and im wondering if there's a way of doing this better?

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)，所以我不会让你厌烦那个(除非你不这么想).差不多，有没有更好的方法来做到这一点，如果有，怎么做?

The stored procedure is rather big and intense and I do not think there's a way of having to avoid looping through the records of the Inserted table in one way or another (ok, maybe there is, but I'd like to be able to read the code too :p), so I'm not gonna bore you with that one (unless you like to think otherwise). So pretty much, is there a better way of doing this, and if so, how?

请求后，这里是 sproc

Well after request, here's the sproc

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

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

After help, here's my final result, posted in case others have a similiar issue

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 :)

It looks slightly different now because I had to index it per advertisement too - but thanks alot for the help - sped everything up from 30hour+ to 30 sec to generate the CachedStats from my own development Stat table :)

推荐答案

处理这些情况的诀窍是将顺序操作(为每个记录做 xyz)变成基于集合的操作(更新语句).

The trick with these kinds of situations is to turn the sequential operation (for each record do xyz) into a set-based operation (an UPDATE statement).

我已经分析了您的存储过程并将您单独的 UPDATE 语句合并为一个.然后可以将这条单一语句转换为一个版本，该版本可以一次应用于所有插入的记录，从而无需存储过程，从而无需游标.

I have analyzed your stored procedure and merged your separate UPDATE statements into a single one. This single statement can then be transformed into a version that can be applied to all inserted records at once, eliminating the need for a stored procedure and thereby the need for a cursor.

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

Below is the code that we finally got working. Execution time for the whole operation went down from "virtually forever" (for the original solution) to something under one second, according to the OP's feedback. Overall code size also decreased quite noticeably.

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 确认).

The operations involving the CachedStats table will greatly benefit from one multiple-column index over (Advertid, CustomerId, [Date]) (as confirmed by the OP).

相关文章