while 在触发器内循环以遍历 sql 中表的所有列

while loop inside a trigger to loop through all the columns of table in sql(while 在触发器内循环以遍历 sql 中表的所有列)

问题描述

我在用户表上有一个像下面这样的触发器，用于插入到审计表中，其中列已更新和以前的值:

I have a trigger like below on user table to insert into the audit table with which column was updated and previous value:

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 循环的建议，以及它将如何影响性能.

But my questions is I have like 100 fields on my table. I can't write 100 if conditions. And i need a suggestion how to use while loop in it, and how is it going to effect the performance.

谢谢

推荐答案

试试这个 -

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

这篇关于while 在触发器内循环以遍历 sql 中表的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！