基于触发器的历史记录

Question

我想做的是找出哪些字段已更新并将更改记录到不同的表中。

DECLARE 
    @BillNo int,
    @column_name varchar(500)  

SELECT @BillNo = BillNo FROM INSERTED
DECLARE HistoryMonitorLoop CURSOR FOR
    SELECT    
        column_name 
    FROM 
        information_schema.columns
    WHERE 
        table_name = 'Shipment';
OPEN HistoryMonitorLoop
FETCH next FROM HistoryMonitorLoop INTO @column_name
WHILE @@Fetch_status = 0
BEGIN
    DECLARE
        @OldValue varchar(500),
        @NewValue varchar(500)
    SET @OldValue = (SELECT @column_name FROM Deleted);
    SET @NewValue = (SELECT @column_name FROM Inserted);
    IF(@OldValue != @NewValue)
    BEGIN
        DECLARE @Comment varchar(5000)
        SELECT @Comment = @column_name + ' Changed from ' + @OldValue + ' to ' + @NewValue
        EXEC ShipmentNote_Insert @BillNo=@BillNo,@CoordinatorID=1,@Comment=@Comment
    END
    FETCH next FROM HistoryMonitorLoop INTO @column_name
END
CLOSE HistoryMonitorLoop
DEALLOCATE HistoryMonitorLoop

正在发生的事情是

SET @OldValue = (SELECT @column_name FROM Deleted);   
SET @NewValue = (SELECT @column_name FROM Inserted); 

将 @OldValue 和 @NewValue 设置为列名而不是列的值 – sql 将其处理为 SET @OldValue =（从已删除中选择“列名”）；

Answer 1

我想做的是找出哪些字段已更新

在 SQL Server 中，有两个函数可以完全满足您的需求。

• Columns_Updated() - 检查是否有一个或多个列/在触发器内插入/删除
• Update() - 检查是否单列在触发器内更新

Answer 2

请参阅此 弹出审计跟踪 它使用循环中的查询而不是游标来执行您想要执行的操作。

Answer 3

这不起作用：

SET @OldValue = (SELECT @column_name FROM Deleted);
SET @NewValue = (SELECT @column_name FROM Inserted);

您在这里尝试动态 sql，这不起作用。 您必须对 SQL 进行硬编码，变量 @column_name 不会动态替换为其值，因为触发器的 SQL 在触发器运行之前会被解析一次。 这样，您（根据您的设置）可能会获得列名称的字面值。

可以获取动态 SQL（通过在另一个进程中连接到服务器，或者在 MySQl 中通过创建准备好的语句），但不可能做到这一点和参考触发器中可用的“神奇”INSERTED 和 DELETED 伪表。

因此，您对 information_schema.columns 的巧妙使用将不起作用。 您可以做的是利用这种聪明才智编写一个存储过程来生成触发器（这实际上是我在编写审计触发器时所做的）。 然后，每当您更改 Shipment 表时，您都必须运行 sp 来生成“创建触发器...”statmentnt，然后运行该生成的语句来重新创建触发器。

Answer 4

我会重新考虑你的整个过程。 如果编写不当，触发器可能会成为巨大的性能杀手。 每当您认为需要使用游标或循环时，请再考虑一下。 您需要以基于集合的方式执行此操作。

我们使用两个表触发器方法。 记录有关何时以及谁更改表的详细信息的表以及包含已更改信息的相关表。 这有助于我们查看一次性更改的所有记录。 我们对每个字段使用更新的语句来填充第二个表，如下所示：

if (update([test]))
  begin
    insert [myAudit].dbo.[mytableAuditLogDetail](AuditLogID, ID, ColumnName,   
                                                 OldValue, NewValue)
    select
      @AuditLogID,
      i.[mytableid]),
      'test',
      convert(varchar(8000), d.[test], 0),
      convert(varchar(8000), i.[test], 0)
    from  inserted i
    inner join deleted d on i.[mytableid]=d.[mytableid]
      and (
      (i.[test] <> d.[test]) or 
      (i.[test] is null and d.[test] Is Not Null) or
      (i.[test] is not null and d.[test] Is Null)
          )         
   end

每次架构更改时，我们都会动态重建触发器代码，但触发器本身不是动态的。 即使我们进行大量导入，我们的触发流程也运行得非常快。