用于更新插入和删除总记录的触发器 [英] 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 deletes but I also need to it work on inserts. Do I need to use a separate trigger? Also, is the use of the cursor necessary or is there a more efficient way?

谢谢！

ALTER TRIGGER [dbo].[updateSourceTotals]
   ON  [dbo].imports
   AFTER INSERT, DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @sourceId int;

    DECLARE deleteCursor CURSOR FOR SELECT DISTINCT sourceId FROM deleted

    OPEN deleteCursor

    FETCH NEXT FROM deleteCursor INTO @sourceId

    WHILE @@FETCH_STATUS = 0
    BEGIN

        UPDATE  sources
        SET     totalImports = (
                    SELECT  COUNT(*) 
                    FROM    imports 
                    WHERE   sourceId = @sourceId
                    )
        WHERE   id = @sourceId

    FETCH NEXT FROM deleteCursor INTO @sourceId
    END

    CLOSE deleteCursor
    DEALLOCATE deleteCursor 

END
GO

推荐答案

如果你真的很喜欢 Trigger 方法(我不推荐它)，那么这是一个更简单，可能更快的方法您当前代码的版本:

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:

ALTER TRIGGER [dbo].[updateSourceTotals]
   ON  [dbo].imports
   AFTER INSERT, DELETE
AS 
BEGIN

    UPDATE  s
    SET     totalImports = (
                SELECT  COUNT(*) 
                FROM    imports i
                WHERE   i.sourceId = s.Id
                )
    FROM    sources s
    WHERE   s.id IN(SELECT sourceId FROM deleted)

END

如果你还想覆盖 INSERTs，应该这样做:

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

ALTER TRIGGER [dbo].[updateSourceTotals]
   ON  [dbo].imports
   AFTER INSERT, DELETE
AS 
BEGIN

    UPDATE  s
    SET     totalImports = (
                SELECT  COUNT(*) 
                FROM    imports i
                WHERE   i.sourceId = s.id
                )
    FROM    sources s
    WHERE   s.id IN(
                    SELECT sourceId FROM deleted
                UNION
                    SELECT sourceId FROM inserted
                  )

END

作为一个额外的好处，它应该也适用于 UPDATEs.

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

澄清一下，在触发器中进行预聚合的问题是，即使在您消除 Cursor 之后，也不是在每个请求上重新计算查询，而是在每个 上重新计算它们修改.

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.

即使是抽象的，如果你做了很多这样的请求，这只是一个胜利，但不要对表格进行太多修改.但是，在活动 DBMS 服务器的实际环境中，即使是这个小优势，您也会失去大部分，因为如果您发出许多这样的请求，那么它们可能会被非常有效地缓存(反过来，因为读取更多是缓存-比写更有效).

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).

这篇关于用于更新插入和删除总记录的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！