title: Design considerations for monitoring SQL database table changes
body: <p>I am working on an assignment to monitor and log various "system-level messages” within our multi-tier .NET 4 WPF, WCF, SQL 2008 application. By messages, I mean details about an event that has occurred in our application, e.g. user logs in and out or data is saved. By logging, I mean that we will be inserting messages into a light-weight SQL table from which clients can poll and display the latest messages.</p>
  
  <p>The source of these messages could come from several different components in our application e.g. Windows Services, WCF Host in IIS, and even stored procedures in the Database. Ultimately these components modify SQL tables each in their own way. So rather than attacking each of these components independently, I’m thinking I might be able to simplify things by saying that the messages need to be “triggered” when certain tables in SQL are modified (updated, inserted).</p>
  
  <p>The first thing that comes to mind would be a trigger on each of the tables which monitor changes and insert records into the light-weight message table. I’ve always (99% of the time) been of the mindset that database triggers are bad news (<a href="https://stackoverflow.com/questions/460316/are-database-triggers-evil">Are database triggers evil?</a>). And I would personally much rather develop and debug C# than a SQL trigger.</p>
  
  <p>So, in the pursuit of alternatives, I’ve come across <a href="http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/" rel="nofollow noreferrer">Using SqlDependency to Monitor Database Changes</a> which discusses using the SqlDependency class to detect changes. A quick proof of concept seems to work; however, after looking at several code samples, it looks like with each change that’s detected, new SqlConnection, SqlCommand, and SqlDependency objects will have to be reinitialized and I will probably need 3 or 4 queries that will need to be monitored. </p>
  
  <p>Are there better options to detecting changes to SQL tables in C#?</p>
  
  <p>Also, trigger bias aside, this seems like round-about way to simply monitor changes in one set of tables just to re-insert data into another table. There will be some other logic that will need to take place that I would rather implement in C#, but should I just write triggers to do this and be done with it?</p>
  
  <p>Thoughts?</p>