Solved

SQL question regarding UPdatedBY or CreatedBY

Posted on 2011-03-10
10
301 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
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…

786 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