在SQL中使用触发器内的while循环来遍历表中的所有列

Question

在SQL中使用触发器内的while循环来遍历表中的所有列

9

我有一个触发器，类似下面这样，在用户表上插入到审计表中，记录更新的列和先前的值：

ALTER TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[tbl_User]
AFTER UPDATE
AS


    declare @fieldname varchar(128) ;
    declare @OldValue varchar(255);
    declare @CreateUser varchar(100) ;
    declare @User_Key int;

    select @CreateUser =i.user_name from deleted i; 
    SELECT @User_Key = i.user_key from inserted i;  

    if update(user_name)
      begin
          select @OldValue=j.user_name from deleted j;  
          set @fieldname = 'user_name';

            insert into tbl_Audit(user_key, field_name, previuos_Value, user_name)
            values(@User_Key ,@fieldname,@OldValue, @CreateUser);

      end

我的问题是，我在表格上有100个字段。我不能写出100个if条件语句。我需要建议如何在其中使用while循环，并且它会如何影响性能。

谢谢

- user1882705

1

请您详细说明一下？我不确定如何使用XML插入到AUdit表中。 - user1882705

使用 FOR XML 将整行转换为 XML，并将 XML 列存储在审计表中。 - JNK

但我只想要更新的列和该列之前的数据...有时在100个字段中可能会有2或3个字段被更新。我只需要这三个字段。 - user1882705

没有其他好的方法来实现你想要的功能。你需要编写一个循环来比较inserted和deleted中的每一列，然后输出它们之间的差异。我告诉你，你想要做的这件事是存在问题的，你应该考虑使用其他的方法。 - JNK

1

我不认为这是唯一的方法，但至少对于写入来说，这可能是最快的方法。 - JNK

显示剩余2条评论

2个回答

-1

尝试使用类似于这个查询 - 它将为给定表的所有列生成If语句。

注意：这还没有完全测试，可能需要更多的调整，但你可以看到它背后的思想。

select 'if update(' + C.name + ')
  begin
      select @OldValue=j.' + C.name + ' from deleted j;  
      set @fieldname = ''' + C.name + ''';

        insert into tbl_Audit(user_key, field_name, previuos_Value, user_name)
        values(@User_Key ,@fieldname,@OldValue, @CreateUser);
  end'
from sys.all_columns C
inner join sys.tables T on C.object_id = T.object_id
where T.name = 'table_name' and T.schema_id = SCHEMA_ID('schema_name')

如果使用while循环可能会导致性能问题，我不会选择它...

- Iza Pastoor

所以在你的查询中，我只需要用我的表名tbl_User替换table_name，对吗？ - user1882705

你能告诉我如何调试这个触发器吗？它在我的表上无法正常工作。 - user1882705

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Devart · Accepted Answer

试试这个 -

ALTER TRIGGER [dbo].[trgAfterUpdate] 

    ON [dbo].[tbl_User]
    AFTER UPDATE

AS BEGIN

    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @DocumentUID UNIQUEIDENTIFIER

    DECLARE cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
        SELECT DocumentUID, ...
        FROM INSERTED

    OPEN cur

    FETCH NEXT FROM cur INTO @DocumentUID, ...

    WHILE @@FETCH_STATUS = 0 BEGIN

        DECLARE 
              @BeforeChange XML 
            , @AfterChange XML

        SELECT @BeforeChange = (
            SELECT *
            FROM DELETED
            WHERE [DocumentUID] = @DocumentUID
            FOR XML RAW, ROOT
        )
        , @AfterChange = (
            SELECT *
            FROM INSERTED
            WHERE [DocumentUID] = @DocumentUID
            FOR XML RAW, ROOT
        )

        INSERT INTO dbo.LogUser (DocumentUID, BeforeChange, AfterChange)
        SELECT @DocumentUID, @BeforeChange, @AfterChange

        -- your business logic 

        FETCH NEXT FROM cur INTO @DocumentUID, ...

    END

    CLOSE cur
    DEALLOCATE cur

END