sql-server – while循环触发器,循环遍历sql中表的所有列

我在用户表上有一个类似下面的触发器,用于插入审计表,更新了哪个列和之前的值：

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.我需要一个如何在其中使用while循环的建议,以及它将如何影响性能.

谢谢

解决方法

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

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!