Connection ids and triggers
-
Hi
I hope someone can help me with this,
I have triggers to write audit records on my tables. The thing is that all of the users log into the db using the same SQL User. so to identify the originator of the audit record, I need to have a name. The environment is disconnected, and so I have a stored procedure which is executed each and everytime they connect. The users have a login name, and a security token (assuming the security token only is unique. The sp updates a small table and puts in the SPID for the connection.
Then, when the trigger fires, it does a lookup to the table for the SPID, and uses the login name to write the audit record.
However, the trigger works sometimes, and not others, which makes me wonder if it is unique to a connection. But I don't really know.
I know the trigger in itself is OK, because I have hardcoded a value for @user-id, and it fired no problem
Here is the sp:
CREATE PROCEDURE dbo.uspCurrentUserUpdate
@UserName VARCHAR(20),
@SecurityToken INTEGER,
@RetVal INTEGER OUTPUT
AS
SET NOCOUNT ON
DELETE FROM CurrentUsers WHERE ConnectionID = @@SPID
IF EXISTS(
SELECT 1 FROM CurrentUsers CU
WHERE CU.UserName = @UserName AND CU.SecurityToken = @SecurityToken)
UPDATE CurrentUsers
SET ConnectionID = @@SPID
ELSE
INSERT INTO CurrentUsers
(UserName,
SecurityToken,
ConnectionID)
VALUES
(@UserName,
@Securitytoken,
@@SPID)
SET @RETVAL = @@RowCount
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
And here is the e trigger which uses the table in question:
CREATE TRIGGER i_AuditAHM ON [AHM] FOR INSERT AS SET NOCOUNT ON
DECLARE @user-id VARCHAR(20)
IF EXISTS (SELECT 1 FROM
WHERE [AuditException] = 0 AND [TableName] = 'AssetHazardousMaterial' )
BEGIN
SET @userid = (SELECT UserName FROM CurrentUsers WHERE ConnectionID = @@SPID)
BEGIN TRAN
INSERT INTO Audit.dbo.AHMAudit
(
AHMAuditID,
[AHMID],
[HMaterialID],
[AssetID],
[RECORDDATE],
AuditAction, AuditBy,AuditDate)
SELECT NEWID(),
A.[AHMID],
A.[HMID],
A.[AssetID],
A.[RECORDDATE],
'INSERT',
GetDate()
FROM [AHM] AS A
INNER JOIN INSERTED AS I ON I.AHMID = A.AHMID
COMMIT
END
GO
I apologise in advance for my failure to spot any obvious errors!!
Thanks for any help, I am desperate!!
Edited by - julliff on 03/06/2002 05:25:45 AM
Edited by - julliff on 03/06/2002 05:28:10 AM
-
Dont you need a where clause on the first update?
Andy
-
DOHHHHH!!!! Thanks, I knew I would end up feeling like a numbnut!!
-
I have added the where clause, which is obviously esential. However, it hasn't fixed my problem. At the moment I am the only one using the db so there is only ever one entry in the table.
I just can't get this to work!!
-
Are you sure you're triggering changes that meet your criteria? I'd move the code from the trigger into a proc and set up a fake inserted table to test against. Remove the transaction code too, I always add after I know everything else is working.
Andy
-
I am pretty sure that the trigger is OK, As when I set @userid to 'somestring', he trigger fred prtfrctly everytime. I am just wondering about the wisdom of using the spid.
-
I guess one question would be are you testing on the same spid you're logging? Not using two different QA windows to test? Another is from your client app if you're disconnecting at what point does the spid disappear - I'd think it would last through the transaction.
Andy
-
Does @@spid actually work in a trigger, I thought triggers actually ran under the scope of system and not the connected user so @@spid doesn't work in a trigger?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
It seems to work sometimes. Which makes me think that the SPID returned is different to that which I am capturing at the point of connection. But to answer your question @@SPID does return something!!
-
I haven't tried this, but it looks promising:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_5mwe.asp
"Microsoft® SQL Server™ 2000 introduces the ability to programmatically associate up to 128 bytes of binary information with the current session or connection. Session context information enables applications to set binary values that can be referenced in multiple batches, stored procedures, triggers, or user-defined functions operating on the same session, or connection. You can set a session context by using the new SET CONTEXT_INFO statement, and then you can retrieve the context string from the new context_info column in the master.dbo.sysprocesses table.
Session context information differs from Transact-SQL variables, whose scope is limited to the current batch, stored procedure, trigger, or function. Session context information can be used to store information specific to each user or the current state of the application, which can then be used to control the logic in Transact-SQL statements."
Andy
-
Thanks Andy, I am going to give it a go. I also found this posted by: Arvind Krishnan SQL Server Support Microsoft Corporation
on google:
"In SQL Server 2000, multiple rows could be returned for the same spid value in sysprocesses, if parallelism is involved. Here is an explanation for sp_who from Books Online that should address your question: The sp_who result set will be sorted in ascending order according to the spid values. In case of parallel processing, sub-threads are created for the specific spid. The main thread is indicated as spid =xxx and ecid =0. The other sub-threads have the same spid = xxx, but with ecid > 0. Thus, multiple rows for that spid number will be returned -- grouped together within that spid's placement in the overall list. The sub-threads will be listed in random order, except for the parent thread (ecid = 0), which will be listed first for that spid."
So, it really is the combination of the spid and the ecid that help to uniquely identify a thread.
Sorry - forgot to put that I don't know if this is relevent!!
Edited by - julliff on 03/07/2002 03:22:22 AM
-
Hi Andy,
I have the same problem to find out the user on the client inside a trigger for auditing. Your approach with the context_info seems to be good but what about connection pooling?
I saw that opening several connections (via VB6) from one client-PC uses sometimes the same SPID. When I set the Context-info it could overwrite previous infos... Am I right?
Is connection-pooling possible across different clients?
Tx
Patrick
Patrick SIMONS, MCP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply