title: SQLCLR trigger vs Windows service. When is it appropriate to use SQLCLR?
body: <p>We have a .NET e-commerce application with a SQL Server 2005 back-end. A new order requires certain "post-processing". These tasks include sending emails, creating files, uploading files to an FTP server, and performing CRUD operations against a WCF data service. The code to perform all these tasks is already in place as several .NET class libraries. </p>
  
  <p>The debate that my team is having is where to place this code. I've written a simple Windows service that polls the db at regular intervals and upon detecting new transactions (based on flags) in the db, it does the necessary thing and logs any errors. An alternative that has been proposed is a SQLCLR INSERT trigger that will set off the processing. </p>
  
  <p>I know it's technically possible to do much (all?) of the above tasks in SQLCLR -- I've even found a number of articles explaining how to consume web services from within SQLCLR, so apparently people are doing this. But I am still hesitant. Was SQLCLR ever intended for this kind of thing? And if not, what could be the practical downside? As for the potential benefits of a SQLCLR trigger over a windows service, I can only see one: less db traffic. We expect very light transaction volume initially, so the windows service will generate some "wasteful" traffic. But the service is on the same box as the database, so it doesn't even affect the network, only internal server resources. </p>
  
  <p>Finally, a third possibility would be to use a SQLCLR trigger to save a simple token on the file system, and use FileSystemWatcher in the Windows service (instead of Timer) to perform the tasks as needed.</p>
  
  <p>Please share your thoughts on the trade-offs of these different approaches or suggest a better alternative.</p>