Avatar of DJ P
DJ P
Flag for United States of America asked on

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.
DatabasesSQL

Avatar of undefined
Last Comment
DJ P

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
D B

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DJ P

ASKER
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?
D B

First line of code above:
CREATE TRIGGER tgrInsertOnMyTable ON Visitor

Open in new window

D B

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
DJ P

ASKER
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
D B

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 P

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
D B

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 P

ASKER
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.
D B

The error states Invalid object name 'dbo.tgrInsertOnMyTable'. dbo.tgrInsertOnMyTable is the trigger, not a column.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
DJ P

ASKER
Sorry about the misread. So is that why the email is not being sent or am I way off base?
D B

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 P

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DJ P

ASKER
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?
D B

Start here and see if you can get it going.
D B

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
DJ P

ASKER
Doug,

Nothing is sending regardless If I try and execute in SSMS.
D B

I thought you said here that you were able to send email through SSMS.
D B

We're you able to add the year/role per the information in my last post?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DJ P

ASKER
Yes. Still a no go. Currently looking at a potential alternate solution.
D B

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 P

ASKER
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!
Your help has saved me hundreds of hours of internet surfing.
fblack61