在触发器中记录更新操作

Question

我有一个UPDATE触发器，它产生如下的INSERTED和DELETED表：

插入式

Id  Name    Surname
1   Stack   Overflow
2   Luigi   Saggese

已删除

Id  Name    Surname
1   Stacks  Overflow
2   Luigi   Sag

我想将此更新捕获到日志表中。我的Log表（对于所有表都是全局的）是这样的（然后我必须处理我的INSERTED和DELETED表）：

Id_Table    Table_Key_Value   Id_Value   Old_Value  New_Value
12345               1          4556645    Stack      Stacks
12345               1           544589   Overflow   Overflows
12345               2           544589   Saggese       Sag

Id_Table是表的系统object_id，我在其中执行了UPDATE语句，Table_Key_Value isUPDATEd列的主键的值，Id_Value是我映射到每个表中每个列的自定义ID。仅当通过UPDATE更改列的数据时，才会记录该列的数据。

我想到了两种方法来做到这一点：

1. 在表上执行SELECT，每列一次：

   INSERT INTO LOG (Id_Table, Table_Key_Value, Id_Value,Old_Value, New_Value)
   

   SELECT 12345, Id, 4556645, D.Name, I.Name
   FROM INSERTED I
   INNER JOIN DELETED D ON I.ID = D.ID
   WHERE D.Name <> I.Name
   
   

   union

   SELECT 12345, Id, 544589, D.Surname, I.Surname
   FROM INSERTED I
   INNER JOIN DELETED D ON I.ID = D.ID
   WHERE D.Surname <> I.Surname

2. 对UDF执行一次选择：

   SELECT CustomFunction(12345,Id, I.Name, D.Name, I.Surname, D.Surname)
   

   FROM INSERTED I
   INNER JOIN DELETED D ON I.ID = D.ID

   CustomFunction (_Id_Table,_Table_Key_Value, _Old_Value_Name, _New_Value_Name, _Old_Value_Surname, _New_Value_Surname)

   INSERT INTO LOG(Id_Table, Table_Key_Value, Id_Value,Old_Value, New_Value)
   VALUES(_Id_Table,_Table_Key_Value, 4556645, _Old_Value_Name, _New_Value_Name)

   INSERT INTO LOG(Id_Table, Table_Key_Value, Id_Value,Old_Value, New_Value)
   VALUES(_Id_Table,_Table_Key_Value, 544589, _Old_Value_Surname, _New_Value_Surname)

还有其他方法吗？什么是最有效和可维护的方法？

Answer 1

在回答之前，首先让我说，我认为最好不要将所有表都记录到一个表中。如果您的数据库增长，您可能最终在Log表上引起严重的争用。另外，必须将所有数据更改为varchar或sql_variant才能放入同一列中，从而迫使其占用更多空间。我还认为，将每个更新的列记录到单独的行（跳过未更新的列）将使您查询变得
非常
困难。您是否知道如何将所有数据汇总在一起，以便对每行的更改，何时何地以及由谁进行综合，合理的了解？我认为，每个表只有一个日志表会容易得多。这样，您就不会遇到使它无法正常工作的问题。

另外，您是否了解SQL Server 2008更改数据捕获？如果您使用的是SQL
Server的Enterprise或Developer版本，请改用它！

除了该问题之外，您还可以使用逻辑UNPIVOT（执行您自己的版本）来做您想做的事情。您不能真正使用Native SQL 2005
UNPIVOT，因为您有两个目标列，而不是一个。这是SQL Server 2005及更高版本的示例，使用CROSS APPLY执行UNPIVOT：

INSERT INTO dbo.LOG (Id_Table, Table_Key_Value, Id_Value, Old_Value, New_Value)
SELECT 12345, I.Id, X.Id_Value, X.Old_Value, X.New_Value
FROM
   INSERTED I 
   INNER JOIN DELETED D ON I.ID = D.ID
   CROSS APPLY (
      SELECT 4556645, D.Name, I.Name
      UNION ALL SELECT 544589, D.Surname, I.Surname
    ) X (Id_Value, Old_Value, New_Value)
WHERE
   X.Old_Value <> X.New_Value

这是用于SQL 2000或其他DBMS的更通用的方法（理论上应该在Oracle，MySQL等中工作-对于Oracle，将其添加FROM DUAL到派生表中的每个SELECT中）：

INSERT INTO dbo.LOG (Id_Table, Table_Key_Value, Id_Value, Old_Value, New_Value)
SELECT *
FROM (
   SELECT
      12345,
      I.Id,
      X.Id_Value,
      CASE X.Id_Value
         WHEN 4556645 THEN D.Name
         WHEN 544589 THEN D.Surname
      END Old_Value,
      CASE X.Id_Value
         WHEN 4556645 THEN I.Name
         WHEN 544589 THEN I.Surname
      END New_Value   
   FROM
      INSERTED I 
      INNER JOIN DELETED D ON I.ID = D.ID
      CROSS JOIN (
         SELECT 4556645
         UNION ALL SELECT 544589
      ) X (Id_Value)
) Y
WHERE
   Y.Old_Value <> Y.New_Value

SQL Server 2005及更高版本确实具有本机UNPIVOT命令，尽管总的来说，即使UNPIVOT可以工作，我还是喜欢使用CROSS
APPLY，因为这样做的灵活性更大。具体来说，本机UNPIVOT命令在这里不起作用，因为UNPIVOT只能定位单个目标列，但您需要两个（Old_Value，New_Value）。将两列连接为单个值（稍后再分离）是不好的；之后再为PIVOT创建无意义的行相关器值是不好的，而且我想不出另一种方式来做到这一点，而这并不是这两种方式的变体。CROSS
APPLY解决方案实际上将是最适合您所描述的确切日志表结构的解决方案。

与我在这里的查询相比，您的方法1的执行效果不佳（比率约为{列数}：1较差的性能）。您的方法2是一个好主意，但仍然不是最佳选择，因为调用UDF会产生较大的开销，此外，您还必须循环遍历每一行（颤抖）。