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

i'm using following code (courtesy of pop rivett's sql blog) create triggers on sql server tables audit trail.

it works great 1 exception. i'm required record made change. written system user field supposed this, works if change made directly in database. if it's done via web app system user web account rather individual user.

each table being modified has username column pulls .net user.identity.name , i'd add every row, when tried access inserted.username or deleted.username encountered errors.

how can add column capture information?

create trigger [dbo].[tr_grading]  on [dbo].[grading]  insert, update, delete     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 match table audited     select @tablename = 'grading'      -- date , user     select @systemname = system_user,            @updatedate = convert(varchar(8), getdate(), 112)                            + ' ' + convert(varchar(12), getdate(), 114)     -- action    if exists (select * inserted)       if exists (select * deleted)            select @type = 'u'       else            select @type = 'i'    else        select @type = 'd'      -- list of columns     select * #ins inserted     select * #del deleted      -- primary key columns full outer join     select @pkcols = coalesce(@pkcols + ' and', ' on')             + ' i.' + c.column_name + ' = d.' + c.column_name                 information_schema.table_constraints pk ,         information_schema.key_column_usage c                pk.table_name = @tablename         , constraint_type = 'primary key'         , c.table_name = pk.table_name         , c.constraint_name = pk.constraint_name      -- primary key select insert     select @pkselect = coalesce(@pkselect+'+','')                         + '''<' + column_name                         + '=''+convert(varchar(100), coalesce(i.' + column_name +',d.' + column_name + '))+''>'''                  information_schema.table_constraints pk ,         information_schema.key_column_usage c            pk.table_name = @tablename         , constraint_type = 'primary key'         , c.table_name = pk.table_name         , c.constraint_name = pk.constraint_name      if @pkcols null     begin          raiserror('no pk on table %s', 16, -1, @tablename)          return     end      select                  @field = 0,          @maxfield = max(ordinal_position)               information_schema.columns               table_name = @tablename      while @field < @maxfield     begin           select @field = min(ordinal_position)          information_schema.columns          table_name = @tablename            , 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             information_schema.columns             table_name = @tablename               , 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 + ''''    + ' #ins full outer join #del d'    + @pkcols    + ' i.' + @fieldname + ' <> d.' + @fieldname     + ' or (i.' + @fieldname + ' null ,  d.' + @fieldname + ' not null)'     + ' or (i.' + @fieldname + ' not null ,  d.' + @fieldname + ' null)'             exec (@sql)    end  end go 

looking @ trigger, dynamic sql @ end aliases inserted i, try i.username inserted, , d.username deleted in select part.

here's quick explanation of what's happening in trigger.

selecting temporary tables

select * #ins inserted  select * #del deleted 

aliasing temp tables in select statement

 #ins full outer join #del d 

edit - how set username variable

i set variables right @ top of procedure/trigger so:

   create trigger [dbo].[tr_grading]      on [dbo].[grading]      insert, update, delete             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 inserted)         set @usernamed = (select top 1 username deleted)