0

I want to implement auditing in my application. I don't want to add boilerplate code throughout applications and also using trigger will have an impact on the performance which cannot be done as most of the data processed is to be shown in realtime.

Is there any other way to do so. I am currently using SQL server 2005.

XYZ
  • 119
  • 1
  • 12
  • 3
    "using trigger will have an impact on the performance" - just about any solution I can think of will have *some* impact on performance - you're moving more data around and that data needs storing. Have you *implemented* triggers and measured an *unacceptable* impact on performance? If so, what sort of data volumes are you dealing with and what would be acceptable? – Damien_The_Unbeliever Apr 27 '15 at 10:13
  • Yup, it had great impact on performance as the data are very huge can go upto 100000~ in one call or more – XYZ Apr 30 '15 at 08:55
  • If you just want to audit changes on few tables, you can always hide your table (renaming? ) and write your own procedures to provide access to those tables, where you can add audit actions. – Tim3880 May 18 '15 at 22:48

4 Answers4

0

If are using NHibernate you could use NHibernate event listeners. If are using EntityLite you could override DataService insert, update and delete methods. If are using Entity Framework you could use profiling techinques as described here If you are using raw ADO.NET then It would be more complex, perhaps you could write an ADO.NET provider wrapper, but this is too much work.

Jesús López
  • 8,338
  • 7
  • 40
  • 66
0

I have found the following article on the topic:

http://solutioncenter.apexsql.com/sql-server-database-auditing-techniques/

Simpler approach would be to create a generic procedure for auditing and use it where necessary to log events that worth auditing.

Stamen
  • 595
  • 4
  • 7
  • The article u referred is using a newer version of visual studio. I want to audit in SQL SERVER 2005. – XYZ Apr 27 '15 at 10:58
0

Would SQL Change Data Capture (CDC) work for you? It needs to be enabled on the database and each table that is to be tracked, but it automatically tracks all changes in a separate table.

tember
  • 1,418
  • 13
  • 32
0

There is another approach to have auditing in SQL server 2005 and next versions, as an alternative for Triggers: use OUTPUT clause for any DML operations. You may refer StackOverflow and MSDN on how to use it. I have implemented PUTPUT clause while DML operations and inserted data into Audittrail table. Best thing about output clause is that they have access to "inserted" & "deleted" tables like triggers.

Otherwise try ChangeTracking feature of SQL server.

Thank you!

Pankajyt
  • 85
  • 1
  • 7