SQL Stored Procedure to Send Email based on a field condition

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.
DJ PAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doug BishopDatabase DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DJ PAuthor 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 DeveloperCommented:
First line of code above:
CREATE TRIGGER tgrInsertOnMyTable ON Visitor

Open in new window

SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Doug BishopDatabase DeveloperCommented:
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.
DJ PAuthor 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 DeveloperCommented:
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.
DJ PAuthor 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 DeveloperCommented:
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

DJ PAuthor 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 DeveloperCommented:
The error states Invalid object name 'dbo.tgrInsertOnMyTable'. dbo.tgrInsertOnMyTable is the trigger, not a column.
DJ PAuthor Commented:
Sorry about the misread. So is that why the email is not being sent or am I way off base?
Doug BishopDatabase DeveloperCommented:
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.
DJ PAuthor 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
DJ PAuthor 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 DeveloperCommented:
Start here and see if you can get it going.
Doug BishopDatabase DeveloperCommented:
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.
DJ PAuthor Commented:
Doug,

Nothing is sending regardless If I try and execute in SSMS.
Doug BishopDatabase DeveloperCommented:
I thought you said here that you were able to send email through SSMS.
Doug BishopDatabase DeveloperCommented:
We're you able to add the year/role per the information in my last post?
DJ PAuthor Commented:
Yes. Still a no go. Currently looking at a potential alternate solution.
Doug BishopDatabase DeveloperCommented:
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.
DJ PAuthor 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.