在MS SQL触发器中处理多个记录 [英] Handling multiple records in a MS SQL trigger
问题描述
我第一次必须在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 Server上更容易一些。
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
存储过程很大而且很紧张,我认为没有一种方法可以避免以一种或另一种方式循环遍历Inserted表的记录(好的,也许有,但是我希望能够读取该代码也是: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?
编辑:请求后,这是存储过程
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
在帮助之后,她e是我的最终结果,以防其他人遇到类似问题时发布
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 )转换为基于集合的操作(一条UPDATE语句)。
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).
这篇关于在MS SQL触发器中处理多个记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!