Title: Getting current user with a sql trigger
Tags: sql;sql-server
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
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'
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
Comment: Aside: always use `SET NOCOUNT ON` within your triggers. See my answer for some alternative code.

Here is the accepted answer: 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
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!
Comment for this answer: FYI: `Current_User` is equivalent to `User_Name()` and both are available in SQL Server 2005-2012  :)
Comment for this answer: Please see @Roman_Pekar's answer - User_name() will give dbo if in sysadmin role
Comment for this answer: yup, looks like you beat me to the answer. Also yours is formatted better and is easier to read +1

Here is another answer: You could create a new table and populate it with a trigger, for example:
create trigger LogTrigger
ON YourTable
FOR INSERT
insert into LogTable  (Col_1, Col_2, Col_3, DataInsert, UserName)
select Col_1, Col_2, Col_3, GETDATE(), User_Name()
from inserted

Here is another answer: Use system_user of suser_name() for that.
user_name() will return dbo if your user is in sysadmin role.
Comment for this answer: If you use system_user, it adds the domain name to the username at least in my case. i.e. `domain_name\user_name`

Here is another answer: Current_user is what you are looking for good sir (depending on what version of sql-server you are running)
http://technet.microsoft.com/en-us/library/ms176050.aspx

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 4, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:08/30/13', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<sql><sql-server>', 'Title': 'Getting current user with a sql trigger', 'ViewCount': 36405, 'id': 18535074, 'user_AboutMe': None, 'user_AccountId': 2719401.0, 'user_DisplayName': 'Morne', 'user_Reputation': 111.0, 'user_Views': 6.0, 'user_id': 2346009.0}}