How to notify a person when there''s an update done to a table
-
Hi, don't know if I have this in the right forum but thought I'd give it a try. Is there any way to create a DTS package or some kind of job that would notify a person when there's an update done to a table? We have a financial application that uses SQL Server 2000 as the backend. What I'd like to do is create something that sends an email when there's a record added to a specific table. How can I do this?
thanks!!!
Jim
-
Here's an example you can try using Northwind. Just build on it.
USE Northwind
GO
CREATE TRIGGER dbo.TableUpdateNotification
ON dbo.Employees
AFTER INSERT, UPDATE
AS
DECLARE @sql varchar(4000)
SELECT @SQL = LastName FROM Inserted
EXEC master.dbo.xp_sendmail
@recipients = 'your@email.com',
@message = @sql
--TESTING--
INSERT INTO Employees
(LastName, FirstName)
VALUES ('Hope', 'Bob')
-
I would recommend using the trigger to insert notification rows into another table. Then set up a job to read that table and send the e-mails. The reason for this is so that e-mail related problems don't cause your transaction to run long or fail and rollback your UPDATE.
-
I concur, as SQL Mail can be flaky at times.
-
Great, I'll give it a shot. Thanks for the help!!
-
The trigger will fire regardless of the e-mail. The trigger is not contained in "@query" for xp_sendmail. In fact, @query is not used. Granted you won't ever receive the information you want, but the trigger will still fire.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply