通过触发器更新用户价值
Name Dept ID
MARK XYZ 25
DENIM ABC 35
SOLO DEF 45
上面是我的表。这里当更新完成时，将执行触发器来获取旧值并将其存储在具有更新的 USERID 的现有日志表中，这里我的要求是当执行删除操作时我需要执行应使用旧值更新同一日志表的触发器操作。当它存储在日志表中时，我在日志表中有一个 USERID 字段，并且应使用日志表中的当前（删除用户 ID）USERID 进行更新。
Name Dept ID
MARK XYZ 25
DENIM ABC 35
SOLO DEF 45
The above is my table.Here when the update is done, a trigger will be executed to get the old values and store that in a existing log table with the updated USERID and here my requirements is when a delete operation is performed i need to perform the trigger operation that should update the same log table with the old values.When it stores in the log table i have a USERID field in log table and that should be updated with current(Deleting User ID) USERID in the log table.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
答案取决于身份验证模式。如果您希望触发器写入的审核日志显示删除给定行的用户的身份，则必须在触发器的范围内知道该用户的身份。有两种方法可以了解身份：SQL 引擎本身可以知道它已验证的用户，或者前端客户端软件可以将用户名传递给正在处理删除的存储过程。如果是后者，存储过程必须在删除行之前使用当前用户值更新基表中的行。
通常情况下，许多真实的个人将分别使用客户端软件（或在网络上）进行身份验证，但与 SQL 引擎的连接是通过可能对应于权限级别的共享伪用户（例如文书用户、经理） -用户）。数据库发现该记录是由“clericaluser”而不是“joe”更新的。因此，数据库身份验证可能不足以满足您的审计目的，您需要将“joe”传递给存储过程。
SQL 引擎可以（但不必）与网络身份验证集成，在这种情况下，触发器将有权访问经过身份验证的用户身份，例如 YOURDOMAIN\YourUser。
The answer depends on the authentication mode. If you want an audit log that is written by a trigger to show the identity of the user who deletes a given row, the identity of that user must be known within the scope of the trigger. There are two ways for the identity to be known: the SQL engine itself can be aware of a user it has authenticated, or the front-end client software can pass the username to a stored procedure that is handling the deletion. If it is the latter the stored procedure will have to update the row in the base table with the current user value before it deletes the row.
It is often the case the many real individuals will authenticate with the client software (or on the network) individually but the connection to the SQL engine is via a shared pseudo-user that might correspond to a permission level (e.g. clerical-user, manager-user). The database sees that the record is being updated by "clericaluser" rather than by "joe". So then the database authentication may be insufficient for your auditing purposes, and you need to pass "joe" to a stored procedure.
The SQL engine can be (but need not be) integrated with network authentication, in which case the trigger will have access to the authenticated user identity, e.g. YOURDOMAIN\YourUser.
我猜你会在这里找到你要找的东西（？）：
http://technet.microsoft.com/en-us/library/ms189799.aspx
Guess you will find what(?) you are looking for here:
http://technet.microsoft.com/en-us/library/ms189799.aspx