Update the user value through trigger

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 o开发者_C百科peration 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.

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.

Guess you will find what(?) you are looking for here: http://technet.microsoft.com/en-us/library/ms189799.aspx