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

（编辑：湘西站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!