Getting current user with a sql trigger

Question

I have the following sql trigger for one of my tables. I'm just wondering if its possible that i can track and add the user that makes a change to the table?

See code below

ALTER trigger [dbo].[Trigger_Audit_Client] on [dbo].[Client] for insert, update, delete
as
declare @type varchar(1) ,
@UpdateDate datetime

if exists (select * from inserted) and exists (select * from deleted)
    select @type = 'U'
else if exists (select * from inserted)
    select @type = 'I'
else
    select @type = 'D'

select  @UpdateDate = getdate()

insert  Client_Audit (Client_Id, ClientName, ClientSurname, TelephoneHome, TelephoneWork, TelephoneCellular, DOB, Gender, ClientIdNumber, Company_Id, Region, City, Email, AddressLine1, AddressLine2, Will_UID, WillCreated, WillLatest, UpdateDate, UpdateType)
select  Client_Id, ClientName, ClientSurname, TelephoneHome, TelephoneWork, TelephoneCellular, DOB, Gender, ClientIdNumber, Company_Id, Region, City, Email, AddressLine1, AddressLine2, Will_UID, WillCreated, WillLatest, @UpdateDate, @type + '_old'
from deleted
insert  Client_Audit (Client_Id, ClientName, ClientSurname, TelephoneHome, TelephoneWork, TelephoneCellular, DOB, Gender, ClientIdNumber, Company_Id, Region, City, Email, AddressLine1, AddressLine2, Will_UID, WillCreated, WillLatest, UpdateDate, UpdateType)
select  Client_Id, ClientName, ClientSurname, TelephoneHome, TelephoneWork, TelephoneCellular, DOB, Gender, ClientIdNumber, Company_Id, Region, City, Email, AddressLine1, AddressLine2, Will_UID, WillCreated, WillLatest, @UpdateDate, @type + '_new'
from inserted

Answer 1

Use system_user of suser_name() for that.

user_name() will return dbo if your user is in sysadmin role.

Answer 2

User_Name(): http://technet.microsoft.com/en-us/library/ms188014.aspx

Syntax

USER_NAME ( [ id ] )

When id is omitted, the current user in the current context is assumed. If the parameter contains the word NULL will return NULL.When USER_NAME is called without specifying an id after an EXECUTE AS statement, USER_NAME returns the name of the impersonated user. If a Windows principal accesses the database by way of membership in a group, USER_NAME returns the name of the Windows principal instead of the group.

ASIDE:

I would change your trigger code to this:

SET NOCOUNT ON;

INSERT INTO dbo.Client_Audit (Cliend_Id, ClientName, ..., UpdateDate, UpdateType)
SELECT Coalesce(i.Cliend_Id, d.Cliend_Id) As Cliend_Id
     , Coalesce(i.ClientName, d.ClientName) As ClientName
     , ...
     , Current_Timestamp As UpdateDate
     , CASE WHEN i.Cliend_Id IS NULL THEN 'D'
            WHEN d.Cliend_Id IS NULL THEN 'I'
            ELSE 'U'
       END As UpdateType
FROM   inserted As i
 FULL
  JOIN deleted As d
    ON d.Cliend_Id = i.Cliend_Id;

Does the same thing but in a cleaner manner (no extra logic and variables, just a single statement).

Any questions just ask!