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.

Community
  • 1
  • 1
Germán
  • 1
  • 2
  • 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. – Sean Lange Jan 14 '16 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](http://dba.stackexchange.com/a/17525/1186). – Aaron Bertrand Jan 14 '16 at 20:27
  • Thanks Sean. It is possible, but it would imply that only logical delete will be allowed in the base table. – Germán Jan 14 '16 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. – Steve Jan 14 '16 at 20:30
  • Thanks, I've decided to add the required fields to the audited tables . – Germán Jan 26 '16 at 18:50

1 Answers1

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

larry_seieroe
  • 1