asked on

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

Lee

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.

Éric Moreau

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

chrislindsay

ASKER

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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.

chrislindsay

ASKER

I need the report triggered immidiately on the table change. It does'n sound as if this solution gives me that....
Thanks

Scott Pletcher

@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?

chrislindsay

ASKER

1-5 mins would be good

Scott Pletcher

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).

Anthony Perkins

Scott,

"Change Tracking" does not require Enterprise Edition (CDC does indeed require it).
Thanks for the clarification.

chrislindsay

ASKER

Using change tracking to track changes in database Thanks