while loop inside a trigger to loop through all the columns of table in sql

Question

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.

Thanks

Can you elaborate please? I am not sure how to insert into AUdit table using XML — user1882705
– user1882705, Commented Jun 11, 2013 at 18:41
Convert the entire row to XML using FOR XML and just store and XML column in your audit table. — JNK
– JNK, Commented Jun 11, 2013 at 18:42
But i want only updated column with previous data of that column...Some time out of 100 there may be 2 0r 3 fields updated. i need only those three fields — user1882705
– user1882705, Commented Jun 11, 2013 at 18:45
I don't think it's the ONLY way but it's probably the fastest way, for writes at least. — JNK
– JNK, Commented Jun 11, 2013 at 19:38

Devart · Accepted Answer · 2013-06-12 07:07:50Z

18

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

answered Jun 12, 2013 at 7:07

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1882705 Over a year ago

I don't need the complete row.I just need the field which has been modified and its previous value.Thanks for your reply though...

Devart Over a year ago

You only need find the difference between @BeforeChange and @AfterChange values.

user1882705 Over a year ago

Can you tell me the best way to compare the these two xml variables

Devart Over a year ago

Please see this topic - social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/….

user1882705 Over a year ago

these two xmls are totally different from normal xml file

|

Iza Pastoor · Accepted Answer · 2013-06-12 10:54:07Z

-1

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…

answered Jun 12, 2013 at 10:54

Iza Pastoor

1921 silver badge4 bronze badges

2 Comments

user1882705 Over a year ago

so in your query i just need to replace table_name with my table name which is tbl_User right?

user1882705 Over a year ago

Can you tell me how to debug this trigger? It is not working on my table.

Collectives™ on Stack Overflow

while loop inside a trigger to loop through all the columns of table in sql

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related