Hello Stack Overflow!
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.
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.
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
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?
EDIT: 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
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 :)