Trigger For Updating Total Records On Both Insert And Delete

I'm writing a trigger to store the record count of one table as a column in another to speed up some reporting queries on a large db. Here's what I've got so far, it works fine on

Solution 1:

If you are really set on the Trigger approach (and I do NOT recommend it) then this is a much simpler and probably faster version of your current code:

ALTERTRIGGER [dbo].[updateSourceTotals]
   ON  [dbo].imports
   AFTER INSERT, DELETEASBEGINUPDATE  s
    SET     totalImports = (
                SELECTCOUNT(*) 
                FROM    imports i
                WHERE   i.sourceId = s.Id
                )
    FROM    sources s
    WHERE   s.id IN(SELECT sourceId FROM deleted)

END

If you want to cover INSERTs also, this should do it:

ALTERTRIGGER [dbo].[updateSourceTotals]
   ON  [dbo].imports
   AFTER INSERT, DELETEASBEGINUPDATE  s
    SET     totalImports = (
                SELECTCOUNT(*) 
                FROM    imports i
                WHERE   i.sourceId = s.id
                )
    FROM    sources s
    WHERE   s.id IN(
                    SELECT sourceId FROM deleted
                UNIONSELECT sourceId FROM inserted
                  )

END

As an added bonus, it should work for UPDATEs as well.

---

Just to clarify, the problem with doing pre-aggregation in a Trigger, even after you eliminate the Cursor, is that instead of re-calculating the query on each request, you are instead re-calculating them on each modification.

Even in the abstract, this is only a win if you do many such requests, but do not modify the table very much. However, in the real context of an active DBMS server, you lose most of even this small advantage too, because if you are making many such requests, then they are probably getting cached very effectively (in turn, because reads are much more cache-effective than writes).