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?

Answer 3

You can use sql notifications to do this but you said you don't want to use the broker. Otherwise you can poll, but as mentioned this can cause performance issues.

Another way to do this is using triggers on the database table to touch a file on the filesystem.

exec master..xp_cmdshell 'echo changed > c:\temp\filewatcher.txt'

Then use a FileSystemWatcher as you mentioned you are already doing in your application to receive a notification when the file changes.

There are security permissions that you will need to grant to your Sql Server user to make this possible but if that is acceptable then this will work without using the broker.

Answer 4

I also recommend trigger that registers inserts somewhere in db (and you must poll database anyway, which is not so expensive if the trigger modifies a special table with a single row). But if you have IDENTITY on your primary key, you can monitor the value of current identity of the table:

SELECT IDENT_CURRENT('TableName')

It's a hack, however, but it's fast and you don't need to modify your database (of course, it works only for insert operations). However you may skip insert operations if between polls identity value was modified explicitly and was set exactly to the value it was at the moment of previous polling (which is not very probable though).

Answer 5

I would consider CLR trigger, Please read following articles...

Create Your First CLR Trigger in SQL Server

CLR Triggers

Answer 6

As per "Damien" you need to use the sqldependency to detect changes.

sample for sqldependency at msdn:- https://msdn.microsoft.com/en-us/library/62xk7953(v=vs.110).aspx

Answer 7

Trigger sounds best option:

create TRIGGER [tI_Notifier] ON [dbo].[your_table_name] AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    declare @id1 int --or same type as your key
        --declare other variables you want to read from the inserted row

        --read columns values from inserted row
    select @id1 = <some_key_column>, @id2=<second_column> from inserted

    --do something with row's new values

    SET NOCOUNT OFF;
END