title: How to add username column into trigger in SQL Server for audit trail
body: <p>My audit trigger,shown below, works great except that I am required to record who exactly made the change. As written the system user field was supposed to do this, but that only works if the change is made directly in the database. If it's done via the web app then the system user is the web account rather than the individual user.</p>
  <p>Each table being modified has a <code>UserName</code> column which pulls from .NET <code>User.Identity.Name</code> and I'd like to add that into every row, but when I tried to access <code>inserted.UserName</code> or <code>deleted.UserName</code> I encountered errors.</p>
  <p>How can I add a column to capture this information?</p>
  <pre><code>CREATE TRIGGER [dbo].[tr_Grading] 
  ON [dbo].[Grading] 
  FOR INSERT, UPDATE, DELETE
  AS
      DECLARE @bit INT ,
              @field INT ,
              @maxfield INT ,
              @char INT ,
              @fieldname VARCHAR(128) ,
              @TableName VARCHAR(128) ,
              @PKCols VARCHAR(1000) ,
              @sql VARCHAR(2000), 
              @UpdateDate VARCHAR(21) ,
              @SystemName VARCHAR(128) ,
              @Type CHAR(1) ,
              @PKSelect VARCHAR(1000)
     
      -- change @TableName to match the table to be audited
      SELECT @TableName = 'Grading'
  
      -- date and user
      SELECT @SystemName = SYSTEM_USER,
             @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) 
                            + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
  
     -- Action
     IF EXISTS (SELECT * FROM inserted)
        IF EXISTS (SELECT * FROM deleted)
             SELECT @Type = 'U'
        ELSE
             SELECT @Type = 'I'
     ELSE
         SELECT @Type = 'D'
  
      -- get list of columns
      SELECT * INTO #ins FROM inserted
      SELECT * INTO #del FROM deleted
  
      -- Get primary key columns for full outer join
      SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') 
             + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
      FROM    
          INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
      WHERE   
          pk.TABLE_NAME = @TableName
          AND CONSTRAINT_TYPE = 'PRIMARY KEY'
          AND c.TABLE_NAME = pk.TABLE_NAME
          AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  
      -- Get primary key select for insert
      SELECT @PKSelect = COALESCE(@PKSelect+'+','') 
                         + '''&lt;' + COLUMN_NAME 
                         + '=''+convert(varchar(100), coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''&gt;''' 
      FROM    
          INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
     WHERE
          pk.TABLE_NAME = @TableName
          AND CONSTRAINT_TYPE = 'PRIMARY KEY'
          AND c.TABLE_NAME = pk.TABLE_NAME
          AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  
      IF @PKCols IS NULL
      BEGIN
           RAISERROR('no PK on table %s', 16, -1, @TableName)
           RETURN
      END
  
      SELECT         
          @field = 0, 
          @maxfield = MAX(ORDINAL_POSITION) 
      FROM 
          INFORMATION_SCHEMA.COLUMNS 
      WHERE 
          TABLE_NAME = @TableName
  
      WHILE @field &lt; @maxfield
      BEGIN  
          SELECT @field = MIN(ORDINAL_POSITION) 
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_NAME = @TableName 
            AND ORDINAL_POSITION &gt; @field
  
          SELECT @bit = (@field - 1 )% 8 + 1
          SELECT @bit = POWER(2,@bit - 1)
          SELECT @char = ((@field - 1) / 8) + 1
  
          IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) &amp; @bit &gt; 0 OR @Type IN ('I','D')
          BEGIN
             SELECT @fieldname = COLUMN_NAME 
             FROM INFORMATION_SCHEMA.COLUMNS 
             WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION = @field
  
             SELECT @sql = '
         
             INSERT Audit(Type, TableName, PK, FieldName, OldValue, NewValue, 
                          UpdateDate, SystemName)
                SELECT 
                    ''' + @Type + ''',
                    ''' + @TableName + ''',
                    ' + @PKSelect + ',''' + @fieldname + ''''
     + ',convert(varchar(1000),d.' + @fieldname + ')'
     + ',convert(varchar(1000),i.' + @fieldname + ')'
     + ',''' + @UpdateDate + ''''
     + ',''' + @SystemName + ''''
     + ' from #ins i full outer join #del d'
     + @PKCols
     + ' where i.' + @fieldname + ' &lt;&gt; d.' + @fieldname 
     + ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
     + ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
             EXEC (@sql)
     END
   END
  GO
  </code></pre>