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

我不得不第一次在MSsql中使用触发器,一般来说是很好的触发器.阅读并自己测试后,我现在意识到触发器会触发每个命令,而不是每行插入,删除或更新.

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

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

我现在的问题是如何使用多个记录来做到这一点.我已经尝试过使用光标,但就我能够阅读和看到自己而言,这表现得非常糟糕.我也考虑过编写一些“检查” – 比如检查命令中是否有多条记录,然后继续使用光标,否则只是避免这种情况.无论如何,这是我的光标解决方案,我想知道是否有更好的方法吗？

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

存储过程相当大而且强烈,我不认为有必要避免以某种方式循环遍历Inserted表的记录(好吧,也许有,但我希望能够阅读代码也是：p),所以我不会厌倦那个(除非你不想另外考虑).所以,有没有更好的方法来做到这一点,如果是这样,怎么样？

编辑：请求后,这是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,@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,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,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,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,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,[Views] = 
            [Views] + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   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,Leads = 
            Leads + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.[Unique] = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   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,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,i.[Date])
                AND   s.AdvertId = i.AdvertId
            ),PublisherOrderValue =
            PublisherOrderValue + ISNULL((
                SELECT SUM(PublisherEarning) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date,i.[Date])
                AND   s.AdvertId = i.AdvertId
                AND   s.[Type] = 3              
            ),AdvertiserOrderValue =
            AdvertiserOrderValue + ISNULL((
                SELECT SUM(AdvertiserCost) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   CONVERT(Date,i.[Date])
                AND   s.AdvertId = i.AdvertId           
                AND   s.[Type] = 3
            ),PublisherCPC = 
            CASE WHEN (Clicks + (
                SELECT COUNT(*) FROM Inserted s
                WHERE s.Approved = 1
                AND   s.PublisherCustomerId = i.PublisherCustomerId
                AND   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,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,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,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,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秒加速所有内容从我自己的开发Stat表生成CachedStats

上一篇：sql – 如何为每个键值选择具有最新下一篇：sql-server – 使用SQL Server T-S

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

猜你在找的MsSQL相关文章