CODEWITHSUNDEEP
sql-server
blay
blay

Reputation: 225

send mail after insertion in sql server

I have a table called regis_tbl and in the table I have a field called Email. What I want to do is I want send an email to the user immediately the user Inserts to the table. Please how do I do that

Upvotes: 0

Views: 1203

Answers (1)

Leonardo Hernández
Leonardo Hernández

Reputation: 191

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

hat 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.
  1. 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).

Upvotes: 1

Related Questions