I am constructing a database that needs to keep track of historical data. It is a Electronic medical system. All data needs to be updated in real time so the user can see what changes happened in the application.

Would CDC be the way to go or should I just have triggers on the database that pushes the information into a Audit tables

  • 2
    Doesn't your previous question answer this one? Presumably the application should not be wiping out historical data if you want to do schema updates.
    – Jon Seigel
    Jun 22, 2013 at 20:14
  • CDC is used more for auditing purposes, not to keep track of a lot of data version changes. Jun 24, 2013 at 17:43
  • Depends on what you want to do. CDC is used more for auditing/data security purposes. If this is what you need, then CDC is better than using triggers. Jun 24, 2013 at 18:05
  • You say "All data needs to be updated in real time so the user can see what changes happened in the application." CDC is not real time! Apr 1, 2014 at 10:49

1 Answer 1


There is no definitive answer here because each solution has its own pros and cons.


  • Easier to setup and you don’t need to worry about creating triggers and storing trigger data

  • Available only in SQL Server Enterprise edition so price may be an issue


  • More flexibility to adopt this to your needs (you can audit sql
    server users, applications and more…)
  • More management and planning will be required upfront. You need to design the triggers, figure out how to store history data, how to do reporting and probably more…

One of these two is enough – you don’t need to implement both.

If you decide to use triggers I’d recommend you find a third party tool that can generate triggers for you, create reports and such. I know that ApexSQL Audit can do this but note that it’s a premium tool (has a free trial though).

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