C# / SQL Listener to tell me if a row has been inserted into a table

Question

Can someone post sample code or tell me how to set up a listener to notify me (trigger an event) if a new row is inserted into a SQL Server database?

I don't want to use any of the SQL Server messaging or broker services. I currently have a file listener which will notify me when new rows are appended to a flat file. I would like to do the same for a database table.

Answer 1

Are you perhaps looking for the SqlDependency class, that lets your code register to be notified when changes occur?

SqlDependency is ideal for caching scenarios, where your ASP.NET application or middle-tier service needs to keep certain information cached in memory. SqlDependency allows you to receive notifications when the original data in the database changes so that the cache can be refreshed.

Or does this fall within the realm of things that you're disallowing, it's not entirely clear?

Answer 2

You would need to do some sort of database trigger on insert or poll the database regularly to look for new records. I don't recommend the latter suggestion as that can be very performance intensive.

Other than that, you aren't giving us much to go on. What version of SQL Server are you using? What have you tried already? What problems have you encountered?

---

Here are a few links that might point you in the right direction:

Exploring SQL Server Triggers

How to: Create and Run a CLR SQL Server Trigger

A similar question that suggests an alternative (better?) way of doing this: Can SQL CLR triggers do this? Or is there a better way?