title: Handling multiple records in a MS SQL trigger
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <pre><code>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
  </code></pre>
  
  <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?</p>
  
  <p>EDIT: Well after request, here's the sproc</p>
  
  <pre><code>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 &gt; 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
  </code></pre>
  
  <p>After help, here's my final result, posted in case others have a similiar issue</p>
  
  <pre><code>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
              )) &gt; 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
              )) &gt; 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
  </code></pre>
  
  <p>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 :)</p>