title: How to get user name who updated the data from UI in MVC 5 and Entity Framework
body: <p>We have a website in ASP.NET MVC 5 with Entity Framework.</p>
  
  <p>When a logged in user made changes in UI (i.e. update the data) we save/update/delete the data in SQL Server as per operation performed by the user.</p>
  
  <p>We also have a trigger for audit trailing. </p>
  
  <p>With the trigger, table format for storing data is as below:</p>
  
  <pre><code>[AuditID]
  [Type] -- Contains operation performed (Insert (I), Update (U), Delete (D))
  [TableName]
  [PK] -- Primary Key
  [FieldName]
  [OldValue] 
  [NewValue] 
  [UpdateDate] 
  [UserName] 
  </code></pre>
  
  <p>We are storing <code>SYSTEM_USER</code> in <code>[UserName]</code> column.</p>
  
  <p>If we have any solution to store the <code>[UserName]</code> who actually made changes from UI instead of <code>system_user</code>?</p>
  
  <p>Do we have any approach to pass <code>[UserName]</code> from application (UI) to the trigger?</p>
  
  <p>Please share your thoughts.</p>
  
  <p>I have one solution - to add <code>UpdatedBy</code> column name in all the tables so that trigger can easily get the value of <code>UpdatedBy</code> column from magic tables or from main tables.</p>
  
  <p>Please suggest best approach.</p>
  
  <p>Below is the trigger used.</p>
  
  <pre><code>CREATE TRIGGER [ids].[tr_AuditEmploee] 
  ON Employee 
  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),
              @UserName VARCHAR(128),
              @Type CHAR(1),
              @PKSelect VARCHAR(1000)
  
      --You will need to change @TableName to match the table to be audited
      SELECT @TableName = 'Employee'
  
      -- date and user
      SELECT         
          @UserName = 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+'+','') 
                      + '''' + COLUMN_NAME 
                      + '=''+convert(varchar(100),
  coalesce(i.' + COLUMN_NAME +', d.' + 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
  
      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].[AuditEmployee] (       Type, 
                 TableName, 
                 PK, 
                 FieldName, 
                 OldValue, 
                 NewValue, 
                 UpdateDate, 
                 UserName)
  select ''' + @Type + ''',''' 
         + @TableName + ''',' + @PKSelect
         + ',''' + @fieldname + ''''
         + ',convert(varchar(1000),d.' + @fieldname + ')'
         + ',convert(varchar(1000),i.' + @fieldname + ')'
         + ',''' + @UpdateDate + ''''
         + ',''' + @UserName + ''''
         + ' 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
  </code></pre>