SQL question regarding UPdatedBY or CreatedBY

HEY All,

I have a column in my Table UpdatedBY or CreatedBY,here i want to insert my appliation name whenever the row is inserted or updated.

i found this link but still am not understand the whole concept


some one will help me is much appreciated
Who is Participating?

Improve company productivity with a Business Account.Sign Up

gery128Connect With a Mentor Commented:
Hello Sha1395,

From the link you provided, this SQL returns the database object owner correctly:


You have any difficulties getting object owner from this script?
By Application name you mean your desktop/web application name or just the database object owner?
Sha1395Author Commented:
Hi Grey128,

Am trying to insert database object owner value, it think "CONTEXT_INFO" am not sure.

Thanks in Advance for your help
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Sha1395Author Commented:
thanks grey128

Am trying to insert my application name,but gone thru msdn i found i can do thru CONTEXT INFO.
Sha1395Author Commented:
DECLARE  @CONTEXT_INFO varbinary(128)
select  @CONTEXT_INFO = cast('WebService' + space(128) as binary(128))

please guide me a going on right path ?
Sha1395Author Commented:
This is the value am getting

Sha1395Author Commented:
DECLARE  @CONTEXT_INFO varbinary(128)
select  @CONTEXT_INFO = cast('WebService' + space(128) as binary(128))
update [Employment] set EmployeeNo = @EmployeeNo,   OriginalHireDate = @OriginalHireDate,	TerminationDate=@TerminationDate,ServiceDate=@ServiceDate,BusinessTitle=@BusinessTitle,	CreatedBy=@CONTEXT_INFO,DateUpdated=getdate()	where EmployeeNo = @EmployeeNo


Open in new window

gery128Connect With a Mentor Commented:
why are you using 'WebService' string and casting it to binary? you want that table's (database) owner (something like 'dbo') or want some hard-corded string like 'WebService' ?
if you want to set it as something like 'WebService' then no need to use context info at all. You are declaring @Context_Info variable, not using in-built Context_Info in your code.

What is the datatype of CreatedBy column?

if you want to set some hard-coded value than use this update query:
update [Employment] set CreatedBy = 'WebService'   (not including all fields here for simplicity)

or if you want to set database owner (e.g. dbo) use this update query:
update [Employment] set CreatedBy = current_user (do not prefix @ with current_user)
Sha1395Author Commented:
Hey Grey128

Thanks again,i just want to use in built context info in my code.

But i just seen one example i was following the same,in a result i got the above code.

so what i suppose to modify in my code if want to use Context Info ?
gery128Connect With a Mentor Commented:
Hello Sha1395,

you can declare and access context info like this:
Declare @con_info As nvarchar(50) = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)

Now @con_info has database owner name, you can use like this:

update [Employment] set EmployeeNo = @EmployeeNo,   OriginalHireDate = @OriginalHireDate,      TerminationDate=@TerminationDate, ServiceDate=@ServiceDate, BusinessTitle=@BusinessTitle,      CreatedBy=@con_info, DateUpdated=getdate()      where EmployeeNo = @EmployeeNo
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.