我不得不第一次在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
