Trigger to alert delete and insert

Question

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

Answer 1

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

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'[email protected]',
@subject=@sbj,
@blind_copy_recipients=N'[email protected], [email protected]',
@body=@Msg ;

Obviously fill the variables - you can pull them from the inserted data. Basic trigger info is easy to find in BOL, we pull the insert with:

(select * from inserted)

then assign to the variables like:

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

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