Web Application to know database update

Question

My web application (ASP.NET 4) connects to a database (SQL Server 2005). For every new row inserted in a certain table, I want the web app can do some process (like send an email to me with new data).

How do I implement the system? My first though is having web app check the table SN column, and have a variable lastProcessedSN. And keep polling every minute. Is it possible to have DB notify web app when there's a new row being inserted?

Please advise, thanks.

EDIT: Sending an email is just an example, not the exact thing I want to do. So this question is focusing on how to implement the trigger on MSSQL table insert to call my program.

Answer 1

you could use SqlDependency`s see

http://www.codeproject.com/KB/database/chatter.aspx

&

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx