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 = 'firstname.lastname@example.org' 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.