基于触发器的历史记录

问题描述：

我想要做的是找出哪些字段已更新并将更改记录到不同的表中。基于触发器的历史记录

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 @[email protected],@CoordinatorID=1,@[email protected] 
    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 = (SELECT 'column_name' FROM Deleted);

那么，脚本是否正常工作？你有错误吗？你是否在测试之前问它是否好像？ – BradC 2009-04-13 22:59:21

和?,最新的问题是什么？ – 2009-04-13 23:24:00

正在发生的事是 SET @OldValue =（SELECT @column_name FROM Deleted）; SET @NewValue =（SELECT @column_name FROM Inserted）; 正在将@OldValue和@NewValue =设置为列名，而不是列的值 – 2009-04-13 23:35:39

答

见本Pop on the Audit Trail它使用一个循环中的查询，而不是一个游标，只是做你想做的事情。

答

这不会工作：

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

你正在尝试动态SQL，这是行不通的。您必须对SQL进行硬编码，变量@column_name将不会被其值动态替换，因为在触发器运行之前触发器的SQL会被解析一次。有了这个，你将（取决于你的设置）可能获得列名的字面值。

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

因此，您聪明地使用information_schema.columns将无法正常工作。你所能做的就是利用这种巧妙的方式编写一个存储过程来生成触发器（事实上，当我必须编写审计触发器时，这是我做的）。然后，无论何时更改Shipment表，都必须运行sp以生成“create trigger ....”statmentnt，然后运行即生成语句以重新创建触发器。

答

我所试图做的是找出哪些领域进行了更新

在SQL Server中有两种功能，做你正在寻找什么。

Columns_Updated() - 检查是否一个或多个列（或多个）/插入/触发
Update() - 检查单个列被触发内更新

答

我会重新考虑你的整个过程。如果书写不当，触发器可能会是巨大的性能杀手。任何时候你认为你需要使用游标或循环，再想一想。你需要以基于集合的方式来做到这一点。

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

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

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

基于触发器的历史记录

相关推荐