Q
Problem solve Get help with specific problems with your technologies, process and projects.

Automated e-mail alerts when tables updated

I would like to get data that resides on a SQL Server database to automatically generate an e-mail note to a user when certain fields are updated on tables that reside on that database. Is this possible?

For example: If I update a field on Table InfoPlan_XYZ, I would like an e-mail message to automatically generate and be sent to JoeSmith whose name is found on table SalesRep.

The easiest way to accomplish this would be to create an UPDATE trigger on InfoPlan_XYZ. The first thing that would need to happen is a comparison of the before and after data. This information is stored in the system tables INSERTED and DELETED. In the below example a comparison is made to see if the productname has changed between the two tables. If these values are different the trigger would then execute xp_sendmail with the appropriate message sent to the specified recipients. To further enhance the code below, a SELECT query could be used to get the right list of recipients from the SalesRep table.

CREATE TRIGGER tr_Upd_ InfoPlan_XYZ ON [dbo].[ InfoPlan_XYZ] 
FOR UPDATE
AS
DECLARE @MessageTo varchar(50)
 
If exists (select i.productid from inserted i inner join deleted d on
i.productid=d.productid where i.productname <> d.productname) 
   BEGIN
     --- insert SELECT code to get a list of recipients from SalesRep table
     SELECT @MessageTo = 'gregr@egewoodsolutions.com'
     exec master.dbo.xp_sendmail @recipients =
     @MessageTo, @subject = 'product change', @message = 'product change'
  END

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close