SQL Alert when table changes

Hi Experts,

I would like to set up an alert on a specific database table when a record is added to receive a report from SQL reporting services or at least some notification of a change in the table.
What is the best way of achieving this?

Thanks

Chris
chrislindsayAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> What is the best way of achieving this? <<

To me, the best way to do this is with Change Tracking.  It's built into SQL Server and will be far less overhead, with guaranteed accuracy, vs. anything you could write yourself.

I think you could build an SSRS report that used the change tracking functions to report changes to you as well.
0
 
Lee SavidgeCommented:
What sort of alert? If you want an email, configure SQL to send email using xp_sendmail and send yourself an email using a trigger.

If this is a high load server then this isn't the best way to do this plus you will potentially get spammed.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Can you build a .Net application to monitor it? In that case, you could use SQLDependency. Check http://emoreau.com/Entries/Articles/2012/03/Using-SQL-Dependency-in-a-Net-application.aspx
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
chrislindsayAuthor Commented:
Hi Ideally I would like a SSRS report to be sent by email (Which i would create)  if the database table is updated.  
How do I do this.  I am not familar with triggers and would like some guidance on how to set this up

Thanks
0
 
Anthony PerkinsCommented:
To me, the best way to do this is with Change Tracking.
Assuming that Scott is referring to CDC (Change Data Capture) you should be aware this is a Enterprise Edition only feature.
0
 
chrislindsayAuthor Commented:
I need the report triggered immidiately on the table change.  It does'n sound as if this solution gives me that....
Thanks
0
 
Scott PletcherSenior DBACommented:
@Anthony:
"Change Tracking" does not require Enterprise Edition (CDC does indeed require it).
CT is also less overhead than CDC.


What is "immediately"?  Within one minute, five minutes, or to the second?
0
 
chrislindsayAuthor Commented:
1-5 mins would be good
0
 
Scott PletcherSenior DBACommented:
You can have a report that refreshes every 5 minutes that uses the Change Tracking function to see any row(s) that have been modified in the last 5 minutes (or maybe even since the report was last viewed by anyone).
0
 
Anthony PerkinsCommented:
Scott,

"Change Tracking" does not require Enterprise Edition (CDC does indeed require it).
Thanks for the clarification.
0
 
chrislindsayAuthor Commented:
Using change tracking to track changes in database Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.