Using a trigger to record audit information vs. stored procedure

Question

Suppose you have the following... An ASP.NET web application that calls a stored procedure to delete a record. The table has a trigger on it that will insert an audit entry each time a record is deleted. I want to be able to record in the audit entry the username of who deleted the record. What would be the best way to go about achieving this? I know I could remove the trigger and have the delete stored procedure insert the audit entry prior to deleting but are there any other recommeded alternative?

If a username was passed as a parameter to the delete stored procedure, is there anyway to get this value in the trigger that's excuted when the record is deleted? I'm just throwing this out there...

Answer 1

Have your application pass down the username of the deleter.

In your deleted sproc:

UPDATE  Customer
SET DeletedBy = @DeletedBy 
WHERE ID = @ID

In your trigger, use that same value:

INSERT INTO MyDeletedLog (ID, DeletedBy)

Answer 2

The trigger context has access to the current user, as seen by the SQL Server (ie. the user used by the ASP to connect to SQL):USER_NAME().

If you don't impersonate the Web user when calling SQL, then the usual trick is to set the current user in the session context, from where it can be retrieved by the trigger code, see Using Session Context Information.

Answer 3

The advantage of using the trigger is that you can be sure it will be universal - any modification to the table will be audited. In terms of how to communicate that to the trigger, you can use a field on the table, say a "LastModifiedBy." The trigger itself could clear that field when executed so that if an update happens without setting the field the audit table would pick up that a user was not supplied.

Answer 4

A trigger always captures audit information: you may have several update paths onto the table and the trigger will deal with them all

Use SET CONTEXT_INFO to pass in the user name to the trigger and CONTEXT_INFO() to read it

Related questions:

• Pass a variable into a trigger
• What’s the best way to audit log DELETEs?
• Getting the Windows login name from server