All geek questions in one place

Using a trigger to record audit information and stored procedure

Suppose you have the following ... An ASP.NET web application that calls a stored procedure to delete an entry. There is a trigger in the table that will insert an audit record every time the record is deleted. I want to be able to write in the audit record the name of the user who deleted the record. What would be the best way to achieve this? I know that I can remove the trigger and delete the stored removal procedure, insert the audit record before the removal, but is there any other alternative?

If the username was passed as a parameter to the stored delete procedure, do you still need to get this value in the trigger that was excluded when the record was deleted? I just drop it there ...

+3
source share
4 answers

Trigger context has access to the current user, as seen from SQL Server (that is, the user used ASP to connect to SQL) USER_NAME().

If you do not impersonate the web user when invoking SQL, then the usual trick is to set the current user in the context of the session, from where it can be obtained by the startup code, see Using session context information .

+2
source

, , - . , , , "LastModifiedBy". , , , , .

+2

Apply your application to the username used for deletion.

In remote sproc:

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

In your trigger use the same value:

INSERT INTO MyDeletedLog (ID, DeletedBy)
0
source

Source: https://habr.com/ru/post/1741847/

More articles:


All Articles