Logging field changes in table using triggers or service broker

• DAVE-

  SSC Veteran

  Points: 226

  More actions

  November 27, 2012 at 4:37 am

  #393674

  When someone changes a value in a table I want to retain the old value(s) for a time in a log table. I could implement this as a trigger - is there any performance gain from implementing this using Service Broker? i.e. to offload some of the burden from the UPDATE query?

  Thanks

• ESAT ERKEC

  SSCertifiable

  Points: 5661

  More actions

  November 27, 2012 at 5:20 am

  #1562610

  I think triger is best solution in sql server 2005 i used it about 20 million record table logging

  but you can look at this

  http://sqlblog.com/blogs/linchi_shea/archive/2012/01/24/performance-impact-sql2008-r2-audit-and-trace.aspx

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  November 28, 2012 at 6:41 am

  #1563098

  This should be done with a properly coded trigger.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service