SQL Stored Procedure to Send Email based on a field condition

DJ P
DJ P used Ask the Experts™
on
Hello. I want to setup a trigger that sends an email based on a field condition in my sql database. The field in the databased is called UserDef5 and if the filed does not equal United States then send an email where the body of the email shows visitors firstname and last name (fieldname in table is FirstName, LastName) with the subject of the email to say: Potential Foreign National.

I already created a profile via the database mail configuration wizard called MonitorEmailProfile and did a successful email test.

What I don't know is how to setup the simple syntax of the stored procedure along with the logic of not sending a duplicate email multiple times if the record has already been read.

I would then setup an job to run every xx minutes to run. Hope that makes sense.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Developer
Commented:
You could potentially do this in a INSERT trigger. That would guarantee that it will only be done once. How the trigger is set up depends on how data will be inserted into the table. Will it be a batch job where more than one row will be inserted into the table at a time, or will it be through some front end where only one person row will be inserted at a time. It will definitely be easier if there will be only one row inserted at a time.
CREATE TRIGGER tgrInsertOnMyTable ON myTable
FOR INSERT 
AS
DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)
DECLARE @Country VARCHAR(50)
DECLARE @Body VARCHAR(MAX)
SELECT @Country = UserDef5, @FirstName = FirstName, @LastName = LastName  FROM inserted
IF @COuntry <> 'United States'
BEGIN
    SET @Body = 'Potential foreign national added to the database: ' + @FirstName + ' ' + @LastName
    EXECUTE msdb.dbo.sp_send_dbmail @subject='Potential Foreign National', @body = @Body, @recipients = 'whoever@wherever.com;someone@nowhere.com'
END

Open in new window

It will need to be handled differently if more than one row can be inserted at a time as this would fail.

Author

Commented:
It will be a front end form where only one person would be inserted at a time. The front end will be a webform so multiple entries can take place throughout the day (if that means anything). Also the table name is Visitor which I forgot to mention. Where would I define this per your suggestion?
Doug BishopDatabase Developer

Commented:
First line of code above:
CREATE TRIGGER tgrInsertOnMyTable ON Visitor

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Doug BishopDatabase Developer

Commented:
You can expand on the contents of @Body as you see fit. I just provided a small example to use. See here for full explanation of sp_send_dbmail and all the parameters that are available. You might also want to set @reply_to (if someone hits Reply to the email, where it will be routed to, and @from_address, which can be used to override the values set in the profile.

Author

Commented:
Thanks. Pardon my ignorance here so I opened up a new query, cut and pasted what you have and change the email to the appropriate recipients. The query says commands completed successfully but no email was received.

When I look in the sysmail_allitems, nothing is there. Also when I go to the triggers folder and select modify on the trigger that was created on this line

ALTER TRIGGER [dbo].[tgrInsertOnMyTable] ON [dbo].[Visitor]

it shows an error

Invalid object name dbo.tgrInsertOnMyTable
Doug BishopDatabase Developer

Commented:
I assume you have a default either private or public profile set up? If you do not specify @profile_name, sp_send_dbmail will use the default private profile, if one was set up. If no default private profile exists, it will use the default public profile. If there is neither a default private or public profile, you must specify @profile_name and give it the name of an existing profile for the server. I am not sure what happens if you need to and don't specify one as we've always had a default public profile. I would think sp_send_dbmail would return an error, but it may not and just make you think it sent the email.
I assume you ran from SSMS? If so, was anything returned. I normally get a message that states:
Mail (Id: nnn) queued.

Author

Commented:
Yes, I have a profile created. Here is what I ran. I changed the email (obviously).

CREATE TRIGGER tgrInsertOnMyTable ON Visitor
FOR INSERT
AS
DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)
DECLARE @Country VARCHAR(50)
DECLARE @Body VARCHAR(MAX)
SELECT @Country = UserDefined5, @FirstName = FirstName, @LastName = LastName  FROM inserted
IF @COuntry <> 'United States'
BEGIN
    SET @Body = 'Potential foreign national added to the database: ' + @FirstName + ' ' + @LastName
    EXECUTE msdb.dbo.sp_send_dbmail
      @profile_name ='MonitorEmailProfile',
      @subject='Potential Foreign National',
      @body = @Body, @recipients = 'Test@Test.com'
END

Nothing shows sent when looking in the sysmail_all items. When I go to the triggers folder and select modify,  it shows an invalid column name (see attached).
screenshot.docx
Doug BishopDatabase Developer

Commented:
So, the trigger exists, you can ight-lick and Modify it, but you get an error stating it does not exist. That does not make sense. What add-on are you using that provides that 'tip'? I am pretty sure it is not coming from SSMS.

Have you successfully sent mail outside of this code? Run this and see what you get:
DECLARE @FirstName VARCHAR(50) = 'John'
DECLARE @LastName VARCHAR(50) = 'Doe'
DECLARE @Country VARCHAR(50) = 'Brazil'
DECLARE @Body VARCHAR(MAX)

SET @Body = 'Potential foreign national added to the database: ' + @FirstName + ' ' + @LastName
EXECUTE msdb.dbo.sp_send_dbmail 
      @profile_name ='MonitorEmailProfile',
      @subject='Potential Foreign National', 
      @body = @Body, @recipients = 'Test@Test.com'

Open in new window

Author

Commented:
No. The trigger does exist. What I am saying is when selecting modify on the trigger it shows an invalid column name per my attached screen screen shot.

Yes, I have sent email successfully using the wizard and tried what you sent above and that worked as well.
Doug BishopDatabase Developer

Commented:
The error states Invalid object name 'dbo.tgrInsertOnMyTable'. dbo.tgrInsertOnMyTable is the trigger, not a column.

Author

Commented:
Sorry about the misread. So is that why the email is not being sent or am I way off base?
Doug BishopDatabase Developer

Commented:
I'm not sure what is going on with that. Obviously, the trigger does exist because you are able to pull up the code. What happens if you execute the code to actually alter the trigger. Even though no change has been made it should still run the alter statement.

Author

Commented:
Yes, not saying the trigger does not exist. I'm saying when executing I receive no email and when checking the msdb to see if any email was sent, it shows nothing. Please see attached that outlines my steps. I guess in summary the trigger does not appear to send the email even though there are no errors indicated.
update.docx

Author

Commented:
I think I see whats going on. After the trigger was created if I go to my web form, fill out the information and hit the submit button I get the following error on the form:

InsertNewVisitor error: The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

Once I delete the trigger the form submits (minus sending the email of course).

So where do I go from here?
Doug BishopDatabase Developer

Commented:
Start here and see if you can get it going.
Doug BishopDatabase Developer

Commented:
It works for you as a local user through SSMS because you probably either have an admin role or are a member of a role that has access to sp_send_dbmail.

Author

Commented:
Doug,

Nothing is sending regardless If I try and execute in SSMS.
Doug BishopDatabase Developer

Commented:
I thought you said here that you were able to send email through SSMS.
Doug BishopDatabase Developer

Commented:
We're you able to add the year/role per the information in my last post?

Author

Commented:
Yes. Still a no go. Currently looking at a potential alternate solution.
Doug BishopDatabase Developer

Commented:
What kind of authentication is your web app using against SQL Server?

Sorry about the typo in my last post. Auto correct using my mobile phone comes up with some strange corrections sometimes. It was suppose to say user/role, which I suppose you figured out.

Author

Commented:
Doug,

Thanks for the help! I appreciate hanging in there with me. Turns out I was selecting the wrong user for permissions per your suggestion. Once I granted the correct user and role, your code worked like a charm!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial