StrataFrame Forum » .NET Forums » General .NET Discussion » SQL Server Event Handler

SQL Server Event Handler

Tim Dol — Thu, 10 Sep 2009 05:04:04 GMT

I need to be able to detect when a row is written to a SQL Server table. Is it possible to create an event handler or something like that, so I can execute some VB.NET code whenever a record is written.

Thanks,
Tim

RE: SQL Server Event Handler

Trent L. Taylor — Thu, 10 Sep 2009 05:04:04 GMT

You will want to use SQL Server Notification Services. That is the purpose and intent of SQL Server Notification Services (kindof) to notify you once a notification is registered. For example, if you execute a query on the SQL Server you can have SQL Server notify you when anything within that query has changed. It works pretty well if you get all of the SQL Server/Windows permissions setup correctly, but this can be a bit cumbersome as well.

There are several other ways to do this as well. SQL Server Notification Services can be messy and difficult to work with. Generally what I will do is create a checksum query that I call on a thread that checks to see if there are any changes to any records. If you are looking for INSERTs only then it is very simple as you can just get a SUM() of all of the PKs (if they are numeric) or create an aggregate equivalent to tell you otherwise if there are any new records. For any modifications, if you have a row version field, then you can use a SUM() of the row version to determine if there are any changes.

If you can take this approach you will be more in control and move more quickly. But these are just a couple of ideas. Hope they help. :)

RE: SQL Server Event Handler

Keith Chisarik — Wed, 09 Sep 2009 22:48:35 GMT

that sounds......unpleasant.

RE: SQL Server Event Handler

Tim Dol — Wed, 09 Sep 2009 17:56:49 GMT

I should have provided some further details.

We are doing some integration to a third party SQL Server database and would like to know if I can detect when a record is written to a specific table. We don't have control on what/when data gets written and I can't touch the database in anyway.

I could write a SF windows service to periodically check the table for a record count, on a timer or something like that, however the data is written sporadically throughout the day and I was concerned about having a service continually check for a record. I was just looking for a better way.

RE: SQL Server Event Handler

Keith Chisarik — Wed, 09 Sep 2009 17:43:36 GMT

That is on the database side, if you are wanting to do this strictly within Strataframe at the application level, I think there is an AfterAddNew event (or something real close, I dont have SF loaded where I am right now)

RE: SQL Server Event Handler

Keith Chisarik — Wed, 09 Sep 2009 17:41:01 GMT

What you want is a trigger, these are defined at the database level. You can set them up for inserts, updates, deletes etc.