Clever Geek Handbook

Trigger for warning delete and insert

How can we create a trigger to send email on any insert or delete in a table. It should send the email and also have the location of the server and database. Please let me know.

0


source to share


1 answer


You need to configure dbmail in SqlServer 2005 (if you haven't already).
Inside the trigger, just send something like:

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'me@address.com',
@subject=@sbj,
@blind_copy_recipients=N'you@address.com, himtoo@address.com',
@body=@Msg ;

Obviously fill in the variables - you can pull them out of the inserted data. Basic information about triggers is easy to find in BOL, we insert an insert using

(select * from inserted)


then assign variables like:

SELECT @Msg = MessageText, @Subject= Subject FROM Inserted

Where MessageText and Subject are columns in the inserted row. Just add your own variables for Location and ServerName if you like ...

+1


source






More articles:


All Articles