CODEWITHSUNDEEP
c#sql-server
drew
drew

Reputation: 127

how to be alerted of new insert in SQL Table from .NET?

I need to write some C# code that will monitor a SQL table, and when a new record is inserted, it will create another record in some other table with that newly inserted data. I need to be guarenteed that this will work every time. This will be a 24/7 running windows service as the host.

I can just check by doing a 'select where not in...', but I'm wondering if there's a better way. Is SQL Service broker still the only game in town for this, or has something came a long through the years that is more reliable?

Upvotes: 0

Views: 649

Answers (3)

Brian Pressler
Brian Pressler

Reputation: 6713

MS SQL Server has "Change Tracking" features that maybe be of use to you. You enable the database for change tracking and configure which tables you wish to track. SQL Server then creates change records on every update, insert, delete on a table and then lets you query for changes to records that have been made since the last time you checked. This is very useful for syncing changes and is more efficient than using triggers. It's also easier to manage than making your own tracking tables. This has been a feature since SQL Server 2005.

How to: Use SQL Server Change Tracking

Change tracking only captures the primary keys of the tables and let's you query which fields might have been modified. Then you can query the tables join on those keys to get the current data. If you want it to capture the data also you can use Change Capture, but it requires more overhead and at least SQL Server 2008 enterprise edition.

Change Data Capture

Using these features, you would create your service or SQL Agent job to periodically look at the change tables and do the work necessary with the data that was reportedly changed.

Upvotes: 0

snow_FFFFFF
snow_FFFFFF

Reputation: 3311

If you don't want to do something 100% in the database (like a trigger, etc...), you could use SQL Query Notifications in your C# code:

https://msdn.microsoft.com/en-us/library/t9x04ed2(v=vs.110).aspx

Upvotes: 1

Jigneshk
Jigneshk

Reputation: 348

create trigger on insert.

Trigger

For example, if you have table "Users" and you want to insert the record in Users_Salary table when anybody insert the data in "Users" table.

CREATE TRIGGER trgAfterInsert ON [dbo].[Users] 
FOR INSERT
AS
    declare @usrid int;
    declare @usrname varchar(100);


    select @usrid=i.usr_ID from inserted i; 
    select @usrname=i.usr_Name from inserted i; 


    insert into Users_Salary
           (Users_ID,UserName, Salary,DateTimestamp) 
    values(@usrid,@usrname,10000,getdate());

    PRINT 'AFTER INSERT trigger fired.'
GO

Upvotes: 2

Related Questions