Send e-mail from a trigger
First you need to set up database mail - if you haven't done so, this question might help:
- Scripting setup of database mail
Then you need a trigger:
CREATE TRIGGER dbo.whatever
ON dbo.wherever
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted WHERE speed > 100)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@profile_name = 'default',
@subject = 'Someone was speeding',
@body = 'Yep, they sure were.';
END
END
GO
Now, you're probably going to say you want data from the insert to be actually be included in the e-mail. And your first inclination is going to be to declare some local variables and assign them from inserted
- this doesn't work because your trigger could be responding to a multi-row insert. So the right way to do this is:
CREATE TRIGGER dbo.whatever
ON dbo.wherever
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @body NVARCHAR(MAX) = N'';
SELECT @body += CHAR(13) + CHAR(10) + RTRIM(some_col) FROM inserted;
IF EXISTS (SELECT 1 FROM inserted WHERE speed > 100)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@profile_name = 'default',
@subject = 'At least one person was speeding',
@body = @body;
END
END
GO
That all said, I am not a big fan of sending e-mail from a trigger. Even though database mail uses service broker and so is asynchronous, I would be much more inclined to populate a queue table, and have a background thread that comes around and sends all of the appropriate e-mails. The twothree nice things about this are:
- you minimize the potential delays in committing the outer transaction that fired the trigger - the more complicated your logic in the trigger, the slower you make that process.
- since it is probably not essential that the e-mail is sent the microsecond the row is inserted, you can easily fluctuate the timing of the background process - this avoids having to check the table very minute, all day, when very few times it will ever have to actually do anything.
- As @goodeye pointed out, keeping this process separate can prevent errors in the e-mail part of the process from interfering with the original DML (in their case, an invalid parameter to
sp_send_dbmail
- which I inadvertently suggested - prevented the insert).