Change tracking and immediate notification

Question

Change tracking and immediate notification

Eliyahu

Ten Centuries

Points: 1071
More actions
April 30, 2012 at 7:49 am

#260635

We have the change tracking enable on a table and we have a job that runs once in a while and sends out email reports with all changes since the last run.
Some customers want to get notified immediately as each change happens. What will be the best way of doing that?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Since you aready have a solution that is sending notifications due to change tracking, Instead of adding a trigger or service broker item to send an email immediately, i'd simply schedule that job to run more often than it does now.

since an email is reacted to outside of the SQL server, there's no TRUE reason that it needs to be sent instantly.

schedule it to run every five or ten minutes during biz hours, or even every minute if the table is really receiving that many changes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eliyahu Ten Centuries Points: 1071 More actions · Answer 2

Is there a more immediate way? Perhaps an event in CLR that I could catch?

Lowell SSC Guru Points: 323518 More actions · Answer 3

the true instantaneous way would be a trigger, and your notification (email I assume) would have to be able to report if multiple rows were changed, as well as multiple companyies.

That's probably especially important, since if i updated rows that affected 4 rows for company A and 2 rows for Company B in the same statement, two notifications should go out, right? not 6 notifications(one for each row = email flood)?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!