while loop inside a trigger to loop through all the columns of table in 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

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.

  • ServiceStack OrmLite with multiple Database Servers
  • Write utf-8 to a sql server Text field using ADO.Net and maintain the UTF-8 bytes
  • SQL server 2008 fast retrieval
  • Group Item by Sum of a column
  • using pyodbc on linux to insert unicode or utf-8 chars in a nvarchar mssql field
  • How to cleanse dynamic SQL in SQL Server — prevent SQL injection
  • Thanks

  • append results of sql query
  • Query returns a different result every time it is run
  • SQL Server Function for Maximum Date in Different Fields
  • SQL Server - Create temp table if doesn't exist
  • Connecting to an SQL Server mdf file via PHP
  • SqlDataAdapter.Fill() within a SqlTransaction - is this a bad practice?
  • 2 Solutions collect form web for “while loop inside a trigger to loop through all the columns of table in sql”

    Try this one –

    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
    

    Try using query similar to this one – it will generate If statements for all columns of a given table.

    Note: This is not fully tested and probably needs more adjustments but you see the idea behind it.

    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')
    

    If wouldn’t go with while loop because it can probably cause perf issues…

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.