SQL Stored Procedure to Send Email based on a field condition

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?

First line of code above:

CREATE TRIGGER tgrInsertOnMyTable ON Visitor

Select allOpen in new window

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.

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

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.

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

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'

Select allOpen in new window

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.

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

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

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.

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

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?

Start here and see if you can get it going.

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.

Doug,

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

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

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

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

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.

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!