context_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

General SQL Server Forums

New to SQL Server Programming

context_info

Author

Topic

johnguhan
Starting Member

10 Posts

Posted - 2010-11-11 : 07:07:52

i wanna to use UserID for auditlog (Insert,update and delete trigers),Instead of passing UserID as a parameter,
How to make it this in sql express 2005
Help me out......

john guhan

Sachin.Nand

2937 Posts

Posted - 2010-11-11 : 07:46:41

Can you please elaborate a little more on what exactly you want?

PBUH

johnguhan
Starting Member

10 Posts

Posted - 2010-11-11 : 12:01:07

Thanks for immediate reply,
using trigger(insert,update and delete) to insert into Audit-log table.This is the store procedure which is calling from Triggers.Prob is getting Employee ID as Empty.what may be the problem? Hope u got my point...

DECLARE @EmployeeID NVARCHAR(36)
DECLARE @EntryDate DATETIME

BEGIN
SELECT @EntryDate = GETDATE()

DECLARE @Context VARBINARY(128)
SELECT @Context = context_info FROM master.dbo.sysprocesses WHERE spid = @@SPID
SET @EmployeeID = CAST(@Context AS NVARCHAR(36))

IF @EmployeeID IS NULL OR @EmployeeID = ''
SELECT @EmployeeID = '?'

INSERT INTO AuditTrail
(
CompanyID,
DivisionID,
DepartmentID,
EmployeeID,
EntryDate,
EntryTime,
DocumentType,
TransactionNumber,
TransactionLineNumber,
TableAffected,
FieldChanged,
OldValue,
NewValue
)
VALUES
(
@CompanyID,
@DivisionID,
@DepartmentID,
@EmployeeID,
@EntryDate,
@EntryDate,
@DocumentType,
@TransactionNumber,
@TransactionLineNumber,
@TableAffected,
@FieldChanged,
@OldValue,
@NewValue
)

END

END

[/quote]

john guhan

Sachin.Nand

2937 Posts

Posted - 2010-11-11 : 13:32:01

Is the above code you posted is of trigger or a sp?If it is for a trigger then you can get EmployeedId from inserted table.

Select @EmployeeID=empid from inserted

PBUH

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-11 : 14:34:48

The problem is that you are not SETTING the context info in the SP that causes this trigger code to fire. So sysprocesses doesn't have anything for your spid


--SP code
declare @empID nvarchar(36)
       ,@emp_CI varbinary(128)

set @empID = N'TG'
set @emp_CI = convert(varbinary(128),@empID)
set Context_Info @emp_CI

--trigger code
DECLARE @EmployeeID NVARCHAR(36)
DECLARE @Context VARBINARY(128)
SELECT @Context = context_info FROM master.dbo.sysprocesses WHERE spid = @@SPID
SET @EmployeeID = CAST(@Context AS NVARCHAR(36))

select convert(nvarchar(36),@context) as contextInfo

OUTPUT:
contextInfo
------------------------------------
TG

Be One with the Optimizer
TG

johnguhan
Starting Member

10 Posts

Posted - 2010-11-11 : 21:51:57

Store procedure which is calling from trigger. I'm not passing parameter(Employee ID) from application and having Employee ID column in Audi-log Table only.Wanna to store Employee ID as Global(Session) in SQL, so that we can use anywhere(SP, trigger,function) in SQL.How to make it this?

john guhan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-11 : 22:30:16

Somewhere YOUR code must SET the value. Only then can a child process with the same spid read it from sysprocesses. Did you try my code example? Context_Info is not like global(Session). It is only reliable within the context of a single call.

The best way to audit by employee is to include a column in the subject table(s) you are auditing. That way the value is available in triggers and can be passed as a parameter to functions and SPs.

Be One with the Optimizer
TG

johnguhan
Starting Member

10 Posts

Posted - 2010-11-12 : 00:40:40

I checked out Ur query its fine.U mean to say that have to create column(Employee ID) for All the tables and pass parameter from application.My database having more than 200 tables and 300 Store procedure are there.that why looking for some other way to slove this problem.

john guhan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-12 : 09:50:57

That would be the preferred method, yes. That way you can avoid a bunch of concurrent processes all querying sysprocesses too. But if you want to use context_info you will still have to pass the value from your application to the first SP in your chain. That SP will have to SET context_info and then it will be available for any functions, triggers, and SPs which are called directly or indirectly by the first SP. So you still have to modify your application as well as every SP, function, and trigger. I still think the better option is to add a column to your tables like [LastModifiedBy] or something like that.

Be One with the Optimizer
TG

johnguhan
Starting Member

10 Posts

Posted - 2010-11-12 : 12:59:21

Thanks for nice tips..Need to change first Store Procedure in the application like u said.Audit_Insert Store procedure its calling from triggers from all the tables.its good now.
One more question is:
More than one user access and doing some action in the application by the time how context_info will work it out?

john guhan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-12 : 13:12:02

You're welcome.
By definition concurrent sessions must have different SPIDs even if they are connected as the same application account. So each session will have its own context_info. Just remember that subsequent calls from the same user are not guaranteed to have the same SPID. So you would need to SET it for each call from the application.

Be One with the Optimizer
TG

johnguhan
Starting Member

10 Posts

Posted - 2010-11-14 : 22:24:17

so I have to change All store procedure and query.....
Thanks for fine help...

john guhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources