SQL Server 2012触发器：为每一行执行动态SQL [英] SQL Server 2012 trigger: Execute dynamic sql for each row

问题描述

我在一个表中保留了所有更改（插入，更新，删除）的触发器。当我每次只插入一行时，它工作正常。但是，当我尝试一次插入多行时，我收到此错误：

I have a trigger in a table which keeps all the changes (Insert, Update, Delete). When I insert only one row per time it works fine. But when I am trying to insert multiple rows at once I receive this error :

子查询返回了多个值。当
子查询遵循=，！=，<，< =，>，> =或将子查询用作
表达式时，这是不允许的。

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

这是触发器的代码（我删除了一些不需要缩短代码的部分，例如变量声明等）。

Here is the code of the trigger ( I removed some parts that are not needed to shorten the code like variable declarations etc.)

UPDATE： ：#tempTrigT包含多行时，这些行中的实际错误是：

UPDATE: The actual error is in these lines when #tempTrigT contains more than one rows:

Select * into #tempTrigT from (select * from deleted where @Action in ( 'U','D')) A UNION (select * from inserted where @Action ='I')

    set @sql = 'set @audit_oldvalue=(select cast([' +@Item +'] as NVARCHAR(4000)) from #tempTrigT)';
    EXEC SP_EXECUTESQL @sql,N'@audit_oldvalue sql_variant OUTPUT',@audit_oldvalue OUTPUT -- If inserted @audit_oldvalue gets the new value

    set @sql = 'set @audit_value=(select cast(i.[' +@Item +'] as NVARCHAR(4000)) from dbo.TForms i  inner join #tempTrigT d on i.id = d.id)';
    EXEC SP_EXECUTESQL @sql,N'@audit_value sql_variant OUTPUT',@audit_value OUTPUT

如何我也可以更改它以使其适用于多行吗？

How I can change it to work for multiple rows as well?

推荐答案

您缺少行标识符，因此只能处理一个每个循环行。像这样的

You are missing a row identifier so that you are only handling one row per loop. Something like:

• DECLARE @ID int =（SELECT MIN（id）FROM #tempTrigT）在循环开始时定义一行


• WHERE id = @ID 在整个循环中过滤到该行


• 从#tempTrigT哪里删除id = @ID 在循环结束时，当 id 处理完毕

• DECLARE @ID int = (SELECT MIN(id) FROM #tempTrigT) to define a row at the start of your loop
• WHERE id = @ID to filter to this row throughout the loop
• DELETE FROM #tempTrigT WHERE id = @ID at the end of your loop, when that id is done processing

然后，如果 id 可以在 #tempTrigT 中重复。

Then again, that may not even work if the id can repeat in #tempTrigT.

所有这些...

我绝对会考虑将其分为多个触发器，并通过尝试遍历已删除或已插入的记录并相应地处理所有记录来节省您面临的复杂性。我还将考虑简化您的审核流程。最终目标是能够回顾一下曾经是什么记录，您可以真正简单地实现它们：

I would definitely consider separating this into multiple triggers and save yourself the complexity you are facing by trying to loop through deleted or inserted records and handle them all accordingly. I would also consider simplifying your audit process. The end goal is to be able to look back at what records used to be, which you can achieve really simply:

INSERT INTO [dbo].[AuditTrailTForms] (TForms_Cols, ChangeDate, Change_User, Change_Type)
    SELECT T.*, GETDATE(), COALESCE(ModifiedBy,suser_name()), 'Inserted'
    FROM inserted i
    JOIN TForms T on i.id = T.id

那么您可能会担心它变得更简单查看稍后查询这些表时更改的列值：

Then you can worry about making it easier to view which column values changed later on when you query these tables:

SELECT *
FROM (SELECT *, GETDATE(), 'Current', 'Current'
      FROM TForms
      WHERE ID = @AuditID
         UNION ALL
      SELECT *
      FROM AuditTrailTForms
      WHERE ID = @AuditID
      --AND Change_Type = 
      --AND Change_User = 
     ) T
ORDER BY ChangeDate DESC

编辑：使用身份列：

您可以使用身份colu mn为每个循环定义一行，如下所示：

You can use an identity column to define a row for each loop like so:

DECLARE @TotalRows int = (SELECT MAX(identityColumn) FROM #tempTrigT
DECLARE @RowID int = 1
WHILE @RowID <= @TotalRows
    BEGIN
        --Do stuff
        --For Example
            SET @sql = 'set @audit_oldvalue=(SELECT cast([' +@Item +'] as NVARCHAR(4000)) 
                                             FROM #tempTrigT 
                                             WHERE T.IdentityColumn = @RowID)';
            EXEC SP_EXECUTESQL @sql,N'@audit_oldvalue sql_variant OUTPUT',@audit_oldvalue OUTPUT

        --then increment to the next row when you're done
        SET @RowID = @RowID + 1
    END

这篇关于SQL Server 2012触发器：为每一行执行动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！