We are using SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, around 100 columns and 10K records and increasing rapidly on daily basis. Around 200 users work with this application and updates also occur very frequently. I know handling it via code will require very frequent connections to database. I want to know which is a better approach for doing this. Is handling it through code will provide a better performance than using SQL Server trigger for that.

  • 1
    10K is not a lot of rows. You'd need to define "occur very frequently" more clearly. There are several options for auditing (triggers, CDC, Change Tracking) which you can probably get recommendations for on dba.stackexchange, if you can more clearly define your requirements. May 8, 2013 at 8:25
  • Sorry for not being able to define my requirements clearly. 10K is not a lot of records, but its one day data. There will be about 600-700 insert/update calls per hour on the table.
    – Sandeep Poonia
    May 8, 2013 at 10:32
  • 1
    600-700 calls per hour == 10-11 calls per minute == less than 1 call per second (on average). That's not "frequently" (compared to some systems) May 8, 2013 at 10:38
  • What is the minimum edition of SQL Server you need to code for?
    – Jon Seigel
    May 8, 2013 at 13:39

6 Answers 6


Personally, I would use triggers for auditing.

  • If they fail, then the main DML gets rolled back
  • (same) The main update requires the DML to succeed
  • A table will typically have several DML paths
  • You may not be able to use code
  • You will have direct table update that bypass code at some point

Ok, so we'll use triggers. Some notes

  • Ensure they work for multiple rows: no CURSORs or loops
  • Use them only for auditing: nothing else (no, say, sending emails)
  • Ensure you use TRY/CATCH in the triggers: this massively improves the behaviour on rollbacks in triggers
  • CASCADE foreign keys will cause child table triggers to fire: Beware nesting and recursion and don't use CASCADEs if possible

This last list also addresses sonme points in @NevilleK's answer. Triggers are one tool to use: use wisely and when appropriate and correctly.

600 updates per hour is trivial, not a problem at all for triggers (subject to cursors, loops, sending email and other stupid stuff)

  • 4
    Another benefit of using triggers for auditing it that it catches changes that are made outside the application including those made maliciously by disgruntled employees. Ther are also many times when an ad hoc query is written to change data. Auditing should NEVER be anywhere except the database. Doing it inthe application totally defeats the purpose of auditing.
    – HLGEM
    May 8, 2013 at 14:07

There's an application that will create triggers for you - ApexSQL Audit. You can test the behavior then, but based on what you said, it's not a high transaction database, so there should be no problems. One more resource you can use to audit data changes is reading transaction logs.


I'd probably stick with triggers, for the same reasons gbn described, but if performance becomes an issue, you may want to consider using Service Broker to perform the work asynchronously (if business requirements will allow that). The typical design would be to have a trigger call a stored procedure that sends a Service Broker message, and the Service Broker queue handles the work where latency is occurring.

Here's a short (noncommercial) article I wrote that might help keep any triggers from getting out of hand: Writing Well-Behaved Triggers


You can try using the SQL Server Change Data Capture feature instead of using triggers. In Change Data Capture, the information is retrieved by periodic querying of the online transaction log. The process is asynchronous. Database performance is not affected; performance overhead is lower than with other solutions (e.g. using triggers)

SQL Server Change Data Capture requires no schema changes of the existing tables, no columns for timestamps are added to the tracked (source) tables, and no triggers are created. It captures the information and stores it in tables called change tables. For reading the change tables, Change Data Capture provides table-valued functions


Just a note about triggers regarding of performance: If you have an application that may work frequently with different database engines such, let us say an ERP that should accept any known database engines such as MySQl, MS SQl, ORACL, Inerbbase...etc triggers may become a problem in maintaining the code since triggers syntax is different for each database engine


In general, I'd recommend against using triggers for any heavy lifting - it's not a question of whether the performance is "better" or "worse" - it's that performance becomes unpredictable and issus are hard to debug.

Most performance problems follow a hockey stick curve - performance declines roughly linearly for a while until you hit a bottleneck, and then performance declines exponentially. The trick with scaling your system is to work out where your inflection point is, and what you need to do to move it further away if necessary (e.g. add more RAM, tune your queries, introduce off-line processes etc.).

In my experience, triggers are hard for developers to work with - they are effectively a side effect of the intended action (e.g. they intend to update a record in your table, and as a side effect, the trigger writes and audit entry). I've seen several cases where a change to the "normal" SQL caused the trigger to become much slower as a result of an unintended consequence of the side effect. For instance, a developer who was tuning a slow query changed an index which caused the trigger associated with that table to run MUCH more slowly.

So, I'd recommend against using triggers unless your audit requirements are literally just copying data from one table to another.

  • 1
    My requirement is to just check if data has been updated in some columns (about 20 columns), and if it has been modified then create an audit for it. I'm not favoring Triggers, its just one other way that I know can be used for auditing. I'm asking which one is better, both in terms of implementing and performance. May 8, 2013 at 12:28

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.