0

I need to audit changes for some tables and I want to do it using triggers.

This article Coding the After Delete Trigger in SQL Server represents pretty much what I want to do, that is to say, for every TABLE to audit, I'll have a TABLE_HISTORY where all the changes will be saved when a row is inserted, updated or deleted. The main problem I have is that I want to save the username of the user logged in the application, I have a custom table USERS where I keep this information, but I don't know how to pass it since triggers do not admit parameters.

I've looked this article also Log record changes in SQL server in an audit table , but it uses SYSTEM_USER.

Any help will be appreciated.

Improve this question
5
  • Well as you said you can't pass values to a trigger. If you have a column in the base table for UserDeleted or whatever and you pass that to your delete procedure you could then pick up the value in your trigger. Commented Jan 14, 2016 at 20:17
  • 2
    Have the application put the user name or ID into CONTEXT_INFO(). A similar example (that uses @@PROCID instead of whatever stores your @username or @userid variable) here. Commented Jan 14, 2016 at 20:27
  • Thanks Sean. It is possible, but it would imply that only logical delete will be allowed in the base table. Commented Jan 14, 2016 at 20:28
  • I have done this before by adding the user to the "Application" part of the conection string. Then you can easily get that and parse it in your trigger. Not sure if there is a better way. Commented Jan 14, 2016 at 20:30
  • Thanks, I've decided to add the required fields to the audited tables . Commented Jan 26, 2016 at 18:50

1 Answer 1

Reset to default
0

create duplicate tables of the tables you are auditing and add the users via a join to the table.

example Table Users duplicate Table Users_Audit then just write a trigger to write the changes to the Users_Audit table adding in the date/time and what other information you need for auditing

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.