title: Field level audit on SQL Server using Trigger
body: <p>I am trying to write a trigger which would audit a table's every field - a row's old value and new value in a table. If any of the field has been modified, I need to save the fields old value and the new value along with field name in an audit table, as a new entry.</p>
  
  <pre><code>create trigger Trg_Institution_FieldAudit on Table1 AFTER UPDATE AS
  DECLARE @OldName VARCHAR(30)
  DECLARE @CurrentName VARCHAR(30)
  DECLARE @OldId VARCHAR(30)
  DECLARE @CurrentId VARCHAR(30)
  DECLARE @modifiedBy VARCHAR(30)
  If update(Name)
    BEGIN
      select @OldName = Name from deleted
      select @CurrentName = Name from Inserted
      select @OldId = ID from deleted
      select @currentId = ID from Inserted
      select @modifiedBy = modifiedBy from deleted
      --INSERT statement for Name field alone
      END;
  </code></pre>
  
  <p>This works fine for a small number of fields, but I have a lot of fields (more than 60), and I am not achieving the performance that is required, because of a lot of if conditions. Is there a better way of doing this? On top of this, there are concurrent updates that are happening to around 3 million records in this table, which makes a lot of things go wrong :(</p>
  
  <p><strong>EDIT: Only ONE row will get updated by an UPDATE statement</strong></p>