La manipulation de plusieurs enregistrements dans un MS SQL déclencheur

Je suis d'avoir à utiliser des déclencheurs dans MSSQL pour la première fois, bien déclenche en général. Après avoir lu autour et testé moi-même je me rends compte maintenant que le déclencheur est activé par commande et non par ligne insérée, supprimé ou mis à jour.

La chose entière est quelques statistiques pour un système de publicité. Notre principal stat table est assez grande et ne contiennent pas de données d'une manière qui fait sens dans la plupart des cas. Il contient une ligne par annonce cliqué, vu et etc. En tant qu'utilisateur on est plus enclin à vouloir voir ce que le jour X a Y nombre de clics et le montant de Z de points de vue et ainsi de suite. Nous l'avons fait purement basé sur une requête SQL jusqu'à présent, cette sorte de rapport de la table principale, mais que la table est devenue tellement le temps pour que la requête à exécuter. En raison de cela, nous avons opté pour l'utilisation des déclencheurs de garder un autre tableau mis à jour et donc ce qui en fait un peu plus facile sur le serveur SQL.

Mon problème est maintenant de le faire fonctionner avec plusieurs enregistrements. Ce que j'ai fait est de créer 2 procédures stockées, l'un pour la gestion de l'exploitation d'un insert, et une pour les supprimer. Mon déclencheur d'insertion (écrit pour travailler avec un seul enregistrement) puis graps les données de la table Inserted, et l'envoie directement à la procédure stockée. Le déclencheur de suppression fonctionne de la même manière, et (évidemment?) le déclencheur de mise à jour fonctionne de la même comme un delete + insert.

Mon problème est maintenant la meilleure façon de faire cela avec plusieurs enregistrements. J'ai essayé à l'aide d'un curseur, mais comme ce que j'ai pu lire et voir moi-même, ce réalise vraiment mal. J'ai envisagé d'écrire certains des "contrôles" - comme dans la vérification pour voir SI il y a plusieurs enregistrements dans les commandes, et puis aller avec le curseur, et sinon, il suffit juste d'éviter cela. De toute façon, voici ma solution avec un curseur, et im me demandais si il y a moyen de faire mieux?

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

La procédure stockée est plutôt grand et intense et je ne pense pas qu'il y a un moyen d'avoir à éviter de boucle à travers les enregistrements de la table Insérée d'une manière ou d'une autre (ok, peut-être qu'il est, mais je voudrais être capable de lire le code de trop :p), donc je ne vais pas vous ennuyer avec celui-là (sauf si vous aimez à penser le contraire). Donc, à peu près, est-il une meilleure façon de faire cela, et si oui, comment?

EDIT: bon après la demande de plusieurs, voici la procédure stockée

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

Après l'aide, voici mon résultat final, posté dans le cas où d'autres ont un problème similaire

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

Il ressemble un peu différent maintenant, parce que j'ai eu à l'index par la publicité trop mais merci beaucoup pour l'aide - accéléré de 30hour+ de 30 sec pour générer le CachedStats de mon propre développement Stat table 🙂