Solved

SQL question regarding UPdatedBY or CreatedBY

Posted on 2011-03-10
10
302 Views
Last Modified: 2012-06-21
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

http://stackoverflow.com/questions/323494/sql-server-modifying-the-application-name-property-for-auditing-purposes

some one will help me is much appreciated
0
Comment
Question by:Sha1395
  • 6
  • 4
10 Comments
 
LVL 9

Expert Comment

by:gery128
ID: 35107094
By Application name you mean your desktop/web application name or just the database object owner?
0
 

Author Comment

by:Sha1395
ID: 35114794
Hi Grey128,

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

Thanks in Advance for your help
0
 
LVL 9

Accepted Solution

by:
gery128 earned 500 total points
ID: 35125473
Hello Sha1395,

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

select COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)

You have any difficulties getting object owner from this script?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

Author Comment

by:Sha1395
ID: 35126006
thanks grey128

Am trying to insert my application name,but gone thru msdn i found i can do thru CONTEXT INFO.
0
 

Author Comment

by:Sha1395
ID: 35126015
DECLARE  @CONTEXT_INFO varbinary(128)
select  @CONTEXT_INFO = cast('WebService' + space(128) as binary(128))

please guide me a going on right path ?
0
 

Author Comment

by:Sha1395
ID: 35126017
This is the value am getting

0x444C415765625365727669636520202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020.
0
 

Author Comment

by:Sha1395
ID: 35126029
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

End

Open in new window

0
 
LVL 9

Assisted Solution

by:gery128
gery128 earned 500 total points
ID: 35126796
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)
0
 

Author Comment

by:Sha1395
ID: 35133057
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 ?
0
 
LVL 9

Assisted Solution

by:gery128
gery128 earned 500 total points
ID: 35135392
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question