Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 8 '13 at 11:47

This question came from our site for professional and enthusiast programmers.

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. –  Damien_The_Unbeliever May 8 '13 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 '13 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) –  Damien_The_Unbeliever May 8 '13 at 10:38
    
What is the minimum edition of SQL Server you need to code for? –  Jon Seigel May 8 '13 at 13:39
add comment

5 Answers 5

up vote 8 down vote accepted

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)

share|improve this answer
2  
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 '13 at 14:07
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
    
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. –  Sandeep Poonia May 8 '13 at 12:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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