TSQL替代方法，用于游标循环遍历更新触发数据 [英] TSQL alternative for cursor to loop over Update-trigger data

问题描述

在此案例

由于性能原因，建议不要使用游标。在更新触发器中循环更新数据的最佳实践是什么？

it was suggested that I should not use cursor because of performance reasons. What are the best practices to loop over the update data in an update trigger ?

UPDATE：

以下是创建该更新触发器的TSQL。

The following is the TSQL for the creation of that update trigger.

CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]
ON [dbo].[InfoDocs]
AFTER UPDATE
AS
BEGIN
    DECLARE @infodoctemplateid INT;
    DECLARE @infodocid INT;
    DECLARE @requireccount FLOAT(2);
    DECLARE @filledcount FLOAT(2);
    DECLARE @pcnt FLOAT(2);

    DECLARE c CURSOR FOR
         SELECT id 
         FROM InfoDocs ifd 
         WHERE exists (SELECT 1 FROM Inserted AS i WHERE i.id = ifd.id)

    OPEN c

    FETCH NEXT FROM c INTO @infodocid

    WHILE @@Fetch_Status = 0 
    BEGIN
        SELECT @infodoctemplateid = InfoDocTemplateId 
        FROM InfoDocs 
        WHERE id = @infodocid;

        SELECT @requireccount = COUNT(*) 
        FROM InfoDocTemplateFields 
        WHERE InfoDocTemplateId = @infodoctemplateid 
          AND IsRequired = 1;

        IF (@requireccount = 0)
        BEGIN
            set @pcnt = 100;
        END
        ELSE
        BEGIN
            select @filledcount = count(*) from InfoDocFields 
            where InfoDocId = @infodocid 
            and InfoDocTemplateFieldId in (select id from InfoDocTemplateFields where InfoDocTemplateId = @infodoctemplateid and IsRequired = 1)
            and (BooleanValue is not null or (StringValue is not null and StringValue <> '') or IntValue is not null or DateValue is not null)

            set @pcnt = @filledcount / @requireccount * 100.0;
        END
        update InfoDocs set PercentageCompleted = @pcnt Where id = @infodocid;

        Fetch next From c into @infodocid
    End
Close c
Deallocate c
END

推荐答案

我尝试将光标转换为基于集合的代码，但是我无法测试我的解决方案是正确的，昨晚我没睡很多，所以我可能在这里和那里错过了一些事情，它可能比我编写的代码更短，更有效，但是它应该可以给您带来很好的开始的地方：

I've tried translating your cursor into a set based code, however there is no way for me to test if my solution is correct, and I didn't get much sleep last night so I might have missed some things here and there - and it probably can be a shorter and more efficient code than what I've written, but it should give you a good place to start:

CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]
ON [dbo].[InfoDocs]
AFTER UPDATE
AS
BEGIN
    WITH CTE1 AS
    (
        SELECT  ifd.Id, 
                SUM(CASE WHEN IsRequired = 1 THEN 1 ELSE 0 END) As RequiredCount,
                (
                    select count(*) 
                    from InfoDocFields 
                    where InfoDocFields.InfoDocId = ifd.Id,
                    and InfoDocTemplateFieldId in (
                        select id 
                        from InfoDocTemplateFields 
                        where InfoDocTemplateId = idtf.InfoDocTemplateId 
                        and IsRequired = 1
                    )
                    and 
                        InfoDocFields.BooleanValue is not null 
                        or (InfoDocFields.StringValue is not null and InfoDocFields.StringValue <> '') 
                        or InfoDocFields.IntValue is not null 
                        or InfoDocFields.DateValue is not null

                ) As Filledcount
        FROM InfoDocs ifd 
        JOIN InfoDocTemplateFields idtf
            ON ifd.InfoDocTemplateId = idtf.InfoDocTemplateId
        WHERE exists (SELECT 1 FROM Inserted AS i WHERE i.id = ifd.id)
        GROUP BY ifd.Id, idtf.InfoDocTemplateId
    ), CTE2 AS
    (
        SELECT  ifd.Id, 
                CASE WHEN RequiredCount = 0 THEN 
                    100
                ELSE
                    Filledcount / RequiredCount * 100.0
                END As Completed
        FROM CTE1
    )

    UPDATE docs 
    SET PercentageCompleted = Completed 
    FROM InfoDocs docs
    JOIN cte2 
        ON docs.id = cte2.Id

END

这篇关于TSQL替代方法，用于游标循环遍历更新触发数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！