使用触发器记录审计信息和存储过程。

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?

假设你有以下……一个ASP。调用存储过程以删除记录的web应用程序。该表上有一个触发器，每当删除一条记录时，该触发器将插入一个审计条目。我希望能够在审计条目中记录删除记录的用户名。实现这个目标的最好方法是什么?我知道我可以删除触发器并让delete存储过程在删除之前插入审计条目，但是还有其他可重构的替代方法吗?

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...

如果将用户名作为参数传递给删除存储过程，那么在删除记录时，是否存在被删除的触发器中的这个值?我只是把这个扔出去……

4 个解决方案

#1

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().

触发器上下文可以访问当前用户，如SQL服务器所见。ASP用来连接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.

如果在调用SQL时不模拟Web用户，那么通常的技巧是在会话上下文中设置当前用户，在这里可以通过触发器代码检索当前用户，请参阅使用会话上下文信息。

#2

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.

使用触发器的好处是您可以确保它是通用的——对表的任何修改都将被审计。关于如何与触发器通信，您可以使用表上的一个字段，比如“LastModifiedBy”。触发器本身可以在执行时清除该字段，以便在不设置字段的情况下发生更新时，审计表将发现没有提供用户。

#3

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

使用SET CONTEXT_INFO将用户名传递给触发器，并使用CONTEXT_INFO()读取它

#4

Have your application pass down the username of the deleter.

让您的应用程序传递删除程序的用户名。

In your deleted sproc:

在你删除sproc:

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

In your trigger, use that same value:

在你的触发器中，使用相同的值:

INSERT INTO MyDeletedLog (ID, DeletedBy)

#1