在触发器中记录更新操作 [英] Logging update operation in triggers

查看：125
本文介绍了在触发器中记录更新操作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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



INSERTED 

  Id名称姓氏
 1堆栈溢出
 2 Luigi Saggese

已删除 

  Id名称姓氏
 1堆栈溢出
 2 Luigi Sag

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

  Id_Table Table_Key_Value Id_Value Old_Value New_Value 
 12345 1 4556645堆栈堆栈
 12345 1 544589溢出溢出
 12345 2 544589 Saggese Sag

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



我想过两种方法：


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

     插入日志（Id_Table，Table_Key_Value，Id_Value，Old_Value，New_Value）
    选择12345，Id，4556645，D.Name，I.Name 
     FROM INSERTED I 
     INNER JOIN DELETE D ON I .ID = D.ID 
     W.D.Name<> I.Name 
     
    工会
     
     SELECT 12345，Id，544589，D.Surname，I.Surname 
    从插入的I 
    内联删除D I.ID = D.ID 
     W.D.Surname<> I.Surname


  2. 针对UDF执行一次选择：

      SELECT CustomFunction（12345，Id，I.Name，D.Name，I.Surname，D.Surname）
    从插入I 
    内连接在I.ID = D.ID上​​已删除D 
     
     ** CustomFunction **（_Id_Table，_Table_Key_Value，_Old_Value_Name，_New_Value_Name，_Old_Value_Surname，_New_Value_Surname）
     
    插入日志（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，IddValue，） b $ b VALUES（_Id_Table，_Table_Key_Value，544589，_Old_Value_Surname，_New_Value_Surname）


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

解决方案

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



此外，您是否知道SQL Server 2008 更改数据捕获？如果您使用的是SQL Server的Enterprise或Developer版本，请改用该版本！自己的版本）。您实际上不能使用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 
从
插入我
内部联接删除D我.ID = D.ID 
交叉应用（
 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，向派生表中的每个SELECT添加 FROM DUAL ）：

 插入dbo.LOG（Id_Table，Table_Key_Value，Id_Value，Old_Value，New_Value）
选择* 
 FROM（
选择
 12345，
 I.Id，
 X.Id_Value，
案例X.Id_Value 
当4556645 THEN D.Name 
时544589 THEN D.Surname 
 END Old_Value，
例X.Id_Value 
当4556645 THEN I.Name 
当544589 THEN I.Surname 
 END New_Value 
从
插入I 
内部联接被删除D ON I.ID = D.ID 
交叉联接（
 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会产生较大的开销，此外，还必须循环遍历每一行（抖动）。


I have an UPDATE trigger that produces INSERTED and DELETED table like this:

INSERTED

Id  Name    Surname
1   Stack   Overflow
2   Luigi   Saggese

DELETED

Id  Name    Surname
1   Stacks  Overflow
2   Luigi   Sag

I want to capture this update to a log table. My Log table (that is global for all tables) is like this (then I must process my INSERTED and DELETED table):

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 is the table's system object_id where I have performed the UPDATE statement, Table_Key_Value is the value of the primary key of the UPDATEd columns, Id_Value is a custom ID I mapped to each column in each table. A column's data is logged only if the column is changed by the UPDATE.

I have thought of 2 ways to do this:

  1. Performing a SELECT on the table, once for each column: 

    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. Performing a single select against a 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)

Are there other ways to do this? What is most efficient and maintainable way?

解决方案

Before answering, let me first say that I don't think it is best to log all tables to a single table. If your database grows you could end up with serious contention on the Log table. Plus, all your data has to be changed to varchar or sql_variant in order to be put in the same column, forcing it to take up more space. I also think that logging each updated column to a separate row (skipping columns that aren't updated) is going to make it very hard for you to query. Do you know how to pull all that data together to actually get a composite and sensible view of each row's changes, when, and by whom? Having one log table per table is, in my opinion, going to be much easier. Then you won't have the problems you're experiencing trying to make it work.

Also, did you know about SQL Server 2008 Change Data Capture? Use that instead, if you are using the Enterprise or Developer editions of SQL Server!

Aside from that issue, you can do what you want with a logical UNPIVOT (performing your own version of it). You can't really use the Native SQL 2005 UNPIVOT because you have two target columns, not one. Here's an example for SQL Server 2005 and up using CROSS APPLY to perform the 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

Here's a more generic method for SQL 2000 or other DBMSes (should theoretically work in Oracle, MySQL, etc. -- for Oracle add FROM DUAL to each SELECT in the derived table):

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 and up do have the native UNPIVOT command, though in general, even when UNPIVOT will work, I like using CROSS APPLY instead because there is more flexibility to do what I want. Specifically, the native UNPIVOT command isn't workable here because UNPIVOT can only target a single destination column, but you need two (Old_Value, New_Value). Concatenating the two columns into a single value (and separating later) is not good; creating a meaningless row correlator value to PIVOT with afterward is not good, and I can't think of another way to do it that's not a variation on those two. The CROSS APPLY solution is truly going to be the best for you to match the exact log table structure you've described.

Compared to my queries here, your method #1 will not perform as well (in a ratio of about {the number of columns}:1 worse performance). Your method #2 is a good idea but still suboptimal because calling a UDF has a large overhead, plus then you have to loop over each row (shudder).

这篇关于在触发器中记录更新操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆