sql - 在触发器中记录更新操作

Question

我有一个 UPDATE 触发器，它生成 INSERTED 和 DELETED 表，如下所示:

插入

Id  Name    Surname
1   Stack   Overflow
2   Luigi   Saggese

已删除

Id  Name    Surname
1   Stacks  Overflow
2   Luigi   Sag

我想将此更新捕获到日志表中。我的日志表(对所有表都是全局的)是这样的(然后我必须处理我的 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是我在其中执行 UPDATE 语句的表的系统 object_id， Table_Key_Value is UPDATEd 列的主键值， Id_Value是我映射到每个表中每一列的自定义 ID。仅当列被 UPDATE 更改时才记录列的数据。

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

对表执行 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

对 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

在回答之前，让我先说一下，我认为最好不要将所有表都记录到一个表中。如果您的数据库增长，您最终可能会在日志表上出现严重争用。另外，您的所有数据都必须更改为 varchar 或 sql_variant 才能放在同一列中，从而迫使它占用更多空间。我还认为将每个更新的列记录到单独的行(跳过未更新的列)将使您很难查询。您是否知道如何将所有数据汇总在一起，以实际获得每行更改、何时以及由谁进行的综合且合理的 View ？在我看来，每张表有一个日志表会容易得多。这样您就不会遇到尝试使其工作时遇到的问题。

另外，您是否了解 SQL Server 2008 Change Data Capture ?如果您使用的是 SQL Server 的企业版或开发版，请改用它!

除了这个问题，您可以使用逻辑 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 及更高版本确实具有 native UNPIVOT 命令，但一般来说，即使 UNPIVOT 可以工作，我也喜欢使用 CROSS APPLY，因为可以更灵活地执行我想要的操作。具体来说， native UNPIVOT 命令在此处不起作用，因为 UNPIVOT 只能针对单个目标列，但您需要两个(Old_Value、New_Value)。将两列连接成一个值(然后分开)不好；之后为 PIVOT 创建一个无意义的行相关器值并不好，我想不出另一种方法来做到这一点，而不是这两者的变体。 CROSS APPLY 解决方案真的是最适合您来匹配您所描述的确切日志表结构的。

与我在此处的查询相比，您的方法 #1 不会表现得那么好(比例约为 {列数}:1 更差的性能)。您的方法#2 是一个好主意，但仍然不是最佳选择，因为调用 UDF 的开销很大，而且您必须遍历每一行(不寒而栗)。