Trigger collecting user info - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger collecting user info

Author

Topic

carriehoff
Starting Member

29 Posts

Posted - 2010-09-10 : 10:30:26

Hi all,

I've got some triggers collecting data audit info - and they are working beautifully thanks to all your help here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148890

Some of the changes to tables the triggers are on, are initiated at one of our websites. Obviously, I can track all of the changes, but the one piece of information I'd like the trigger to collect is who is logged onto the website at the time (who is making the changes).

I'm hoping there might be a way to pass in the login info (which is stored in a cold fusion session variable) with a connection string somehow and retrieve it with one of the SQL functions - such as app_name(), or something along those lines.

The website is in coldfusion

and I'm not familiar enough with it to know if what I'm asking is possible. Does anybody here know how to do what I'm asking?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-10 : 11:13:33

Look in Books Online for "CONTEXT_INFO". It's a setting you can use to store information for a connection. You can set its value in your application before you run your SQL or stored procedure, or include it in the stored procedure. An example is shown below:

DECLARE @login VARCHAR(30), @info VARBINARY(128)
SET @login='Scarlett Johansson'  -- this can instead be passed as a parameter
SET @info=CAST(@login AS VARBINARY(128))  -- convert to binary

SET CONTEXT_INFO @info  -- set value

SELECT CAST(CONTEXT_INFO() AS VARCHAR)  -- retrieve value 

SET CONTEXT_INFO 0x0  -- make sure to reset when done

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-10 : 11:16:35

Sorry, forgot to clarify: you'd use SELECT CONTEXT_INFO() in the trigger to get the login name and store in your audit table. The SET CONTEXT_INFO 0x0 reset should also occur in the trigger. The earlier code to set the value occurs in your SQL batch or stored procedure.

carriehoff
Starting Member

29 Posts

Posted - 2010-09-10 : 11:20:24

Ok, thank you I will check it out and report back.

carriehoff
Starting Member

29 Posts

Posted - 2010-09-10 : 11:43:21

Sorry, forgot to mention this is for SQL 2000 - context_info() is not a recognized function name there :(

I'm looking into somehow piggybacking the info in with the app var of the connection string, and then getting that info with the app_name function.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-10 : 13:44:18

You can get it from the sysprocesses system table:

http://msdn.microsoft.com/en-us/library/aa259199(SQL.80).aspx

Use the following query:

SELECT context_info FROM master..sysprocesses WHERE spid=@@spid

You should be able to write a UDF named CONTEXT_INFO() to wrap that query if you prefer.

carriehoff
Starting Member

29 Posts

Posted - 2010-09-10 : 15:03:42

Ok, I am almost with you on this . . .
I pulled up the sysprocesses table and see that there is indeed a context_info field with binary info

Now for your previous post:
DECLARE @login VARCHAR(30), @info VARBINARY(128)
SET @login='Scarlett Johansson' -- this can instead be passed as a parameter
SET @info=CAST(@login AS VARBINARY(128)) -- convert to binary

SET CONTEXT_INFO @info -- set value

SELECT CAST(CONTEXT_INFO() AS VARCHAR) -- retrieve value

SET CONTEXT_INFO 0x0 -- make sure to reset when done
I'm just a little confused about how to get the login info sent from the app to the trigger. You suggested using a parameter, but I am unsure how to send one. I realize now this is probably more a cf questions than SQL, but hopefully you know the answer.

Here's a typical cf update query in our website:
<cfquery name="updateMember"
datasource="#variables.datasource#"
username="#variables.datausername#"
password="#variables.datapassword#">
Update table
set Firstname ='#form.firstname#',
Lastname ='#form.Lastname#',
where memberid = #form.MemberID#
</cfquery>
The login info is stored in a cf session var, but like I said, I'm not sure how to pass that.

Any idea?

carriehoff
Starting Member

29 Posts

Posted - 2010-09-10 : 15:24:31

Hold on . . . I've been reading this article here: http://www.sqlservercentral.com/articles/trigger/70233/
where I can pass the login info to a stored procedure
Set the context_info in the procedure

Then add/update/delete the info,
which will activate the trigger

that can retrieve the context_info before the insert/update/delete and then do whatever else I tell it.

In the example on this page, it looks as though it doesn't matter which SPID it is when you set or retrieve the context_info. Is this true?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-10 : 17:14:47

If you use the CONTEXT_INFO() function, it will return for the current SPID. Since you don't have that function, you have to extract it from sysprocesses, and only for the current SPID (@@SPID). The query I posted will give you that.

As far as ColdFusion, something like this should work:

<cfquery name="updateMember"
datasource="#variables.datasource#"
username="#variables.datausername#"
password="#variables.datapassword#">
declare @info varbinary(128);
set @info=cast('#variables.login#' as varbinary(128));  -- change to the correct variable name
set context_info @info;
Update	table
set Firstname ='#form.firstname#',
Lastname ='#form.Lastname#', 
where memberid = #form.MemberID#
</cfquery>

carriehoff
Starting Member

29 Posts

Posted - 2010-09-13 : 11:06:08

Excellent! I am retrieving the information I need thanks to you robvolk.

If you could please help me with one more thing. The cast to varbinary from varchar is leaving me with extra characters. This is probably because the varchar field is set at varchar(10), but the user information is only 4 characters long, leaving me with 6 additional squares at the end of the field. I am not certain that the information will always be 4 characters which is why I have the field size set as varchar(10).

How can I dump the extra stuff? or prevent it from getting in?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-13 : 11:50:02

Make sure to use varbinary and varchar, not binary or char. Also check if you're using nvarchar (Unicode) anywhere in your CF or SQL code. Nvarchar uses 2 bytes per character, converting this to varbinary could cause problems if you then convert it to varchar.

carriehoff
Starting Member

29 Posts

Posted - 2010-09-13 : 12:06:14

Nope, only using varbinary and varchar - and no nvarchar in CF or SQL

The length of the string being cast to varbinary is 4 characters long.
When I cast the binary back to varchar(10), I get the 4 character login, and six additional squares.
If I cast to varchar(5), I get the 4 character login, and 1 additional square.
It seems to be tied to the varchar() value. I have tried to replace char(13) and char(10) thinking they were line feed characters, but no luck.

What else could they be?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-13 : 12:56:32

My only guess is that CF is padding it somehow. I tested the SQL code and didn't get the extra characters you mentioned. If you can get the binary version and see which characters they map to, you may be able to trim them. For instance, if they're spaces (char(32) 0x20 hex) then an RTRIM would fix it.

carriehoff
Starting Member

29 Posts

Posted - 2010-09-13 : 13:15:23

Yes, that is what I had to do, map the characters (even though I didn't know such a thing was possible). The varbinary to varchar(10) conversion is padding the difference on the right side with 0s (char(0)).

Thanks for all of your help!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-13 : 13:37:16

Hmmm, that's weird, I didn't notice that last time. I thought char(0) in a SQL varchar just stopped processing the string after that point. Anyway, use REPLACE() to change char(0) to ''.

carriehoff
Starting Member

29 Posts

Posted - 2010-09-13 : 14:24:39

Yes, this is SQL 2000 - dunno if that matters . . .
It shows up as little boxes in the SQL Manager.

When I tried to cast to int it wouldn't allow it until I cleared out the trailing characters.

Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources