Trigger Audit Trail Problem - 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 Audit Trail Problem

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2004-10-29 : 15:47:13

Is there any way for a trigger to access the parameters of the currently running stored procedure that is affecting a table?

Due to the need to use connection pooling in my application I can't create a login for each user, and must use one login across all users. This means that the trigger can't just pick up the user currently connected.

Instead I wanted to pass in the ID of the user making the changes to the table to the stored procedure, and have the trigger pick up the ID of the user making the changes and use that. If the user ID was null, *then* the trigger would pick up the currently connected user. That would cover the bases of admins and such making changes to tables.

My original thought was that I could just have a LastUpdatedByUserID column in the table, and have the trigger just use the value from the update, but then this wouldn't accurately reflect who changed the data in the event that an admin or some such manually manipulated the table, and forgot/intentionally didn't change the last updated by column.

Is what I'm looking to do possible? Can I pull parameters from a stored procedure while inside of a trigger?

Thanks,
Steve

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 18:52:05

I can't think of a way, other than storing it in a column in the table.

Is the currently-connected-user obvious? I mean like a single user such as "Web-User" ... if so then if USER_NAME() <> 'Web-User' then the change is being made ad-hoc, so store USER_NAME() in the audit trail - or store it anyway.

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-29 : 19:05:17

You can try SET CONTEXT_INFO and retrieve the login info by querying the context_info column from master..sysprocesses. Something like:

CREATE PROCEDURE myProc @userID int AS
SET NOCOUNT ON
DECLARE @id binary(4) --change this to fit the size of the data being passed
SET @id=cast(@userID as binary(4))
SET CONTEXT_INFO=@id
... --rest of procedure code follows
GO

CREATE TRIGGER myTrigger ON myTable FOR UPDATE AS
SET NOCOUNT ON
DECLARE @id int
SELECT @id=cast(context_info AS int) FROM master..sysprocesses WHERE spid=@@spid
... --rest of trigger code follows
GO

Warning: I've never used CONTEXT_INFO so I haven't tested this code, but Books Online has more details on it. Check it out because the information will persist after the trigger completes, you may have to do more to ensure the context info is only manipulated through that stored procedure.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-29 : 19:32:32

Why not store system_user and the value in the table then you can calculate the actual user later.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Blastrix
Posting Yak Master

208 Posts

Posted - 2004-10-30 : 19:04:39

Great ideas guys.

Kristen, yours is quite simple and elegant. Yes the web user does have it's own specific login that could be checked for.

Rob, your way would require creation of a SQL user for each registered user of the application, correct? I guess it wouldn't be too difficult to create a new login whenever a user is registered. Am I understanding your idea correctly? If not please let me know.

Nigel, are you saying to store both values in the audit table and determine who made the changes when the audit trail is viewed? Wouldn't this then either A) require a third column to indicate which type of user made the change. B) Use a null, or known null value in one of the columns, and the updater in the other?

Thanks again guys.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-30 : 22:31:12

Nope, you don't have to create a SQL login or user. You can store your user ID's in a table and just pass ID's around from your applications, just like any other parameters. The data you'd put into context_info is completely independent of login or user account. Context_info can store anything you like as long as you convert it to varbinary. Books Online explains it better.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-31 : 08:32:29

>> Nigel, are you saying to store both values in the audit table and determine who made the changes when the audit trail is viewed? Wouldn't this then either A) require a third column to indicate which type of user made the change. B) Use a null, or known null value in one of the columns, and the updater in the other?

Nope you use the same logic as you would to determine the user but it means that if the app login changes then the audit trail is still capturing the user

case LastUpdatedLoginByUserID in ('myappuser1','myappuser2') then LastUpdatedByAppUserID else LastUpdatedLoginByUserID end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources