How to add username column into trigger in SQL Server for audit trail

Question

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.

Each table being modified has a UserName column which pulls from .NET User.Identity.Name and I'd like to add that into every row, but when I tried to access inserted.UserName or deleted.UserName I encountered errors.

How can I add a column to capture this information?

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+'+','') 
                       + '''<' + 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 < @maxfield
    BEGIN  
        SELECT @field = MIN(ORDINAL_POSITION) 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @TableName 
          AND ORDINAL_POSITION > @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) & @bit > 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 + ' <> 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

Answer 1

Looking at the trigger, the dynamic SQL at the end aliases inserted as I, so try I.Username for inserted, and D.Username for deleted in the select part.

Here's a quick explanation of what's happening in the trigger.

Selecting into temporary tables

SELECT * INTO #ins FROM inserted

SELECT * INTO #del FROM deleted

Aliasing temp tables in select statement

 from #ins i full outer join #del d

EDIT - How to set username as variable

I like to set variables right at the top of the procedure/trigger like so:

   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),
                @UsernameI VARCHAR(1000),
                @UsernameD Varchar(1000)


        -- Set Username
        SET @UsernameI = (SELECT Top 1 Username FROM Inserted)
        SET @UsernameD = (SELECT Top 1 Username FROM Deleted)