Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raindear
Yak Posting Veteran
64 Posts |
 Posted - 2014-05-29 : 03:32:18
|
| HiI have a SQL table called Customer. This is populated when a sign up form on a website is submitted.On the sign up form there is a field called "Practitioner". If a customer populates this with an email address I want an email to be sent (by the trigger) to the email address saying "This customer has said you are their doctor, please can you confirm yes or no".Here is my triggerCREATE TRIGGER dbo.SEND_MAIL_TO_PRACTITIONERON dbo.customerAFTER INSERTASBEGINDECLARE @PractitionerName VARCHAR(100)DECLARE @body VARCHAR(100)SET @PractitionerName=(SELECT PractitionerName FROM customer )SET @body=(SELECT FirstName + ' ' + LastName from Customer AS CustomerName)+' emailed us saying you were his practitioner, please can you confirm yes or no'IF @PractitionerName IS NOT NULLBEGINEXEC msdb.dbo.sp_send_dbmail@recipients = @PractitionerName,@subject = 'TEST',@body = @body;ENDENDGOThe SQL executes but nothing is being sent. I also think I may have this part wrongIF @PractitionerName IS NOT NULLi'm really new at writing a Trigger. Can anyone see any problems? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-05-29 : 08:41:43
|
| [code]SET @PractitionerName=(SELECT PractitionerName FROM customer )[/code]This statement, while syntactically correct would not do what you want in most cases.The select statement can return multiple rows, and which of those would be assigned to the variable is not predictable. What you probably want is the following:[code]SELECT @PractitionerName = PractitionerName FROM INSERTED[/code]INSERTED is a virtual table that you have available to you in the trigger.Strictly speaking, even this is not correct - because there could be multiple rows in an insert statement and you would be picking up only one.Also, you might want to change IF @PractitionerName IS NOT NULL to IF NULLIF(@PractitionerName,'') IS NOT NULLI have not looked through the rest of your code, so I don't know if the problem is what you stated it is, or it is something else. |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-05-29 : 11:45:04
|
James K is right in that your code, as is, is pulling a random unpredictable PractitionerName from your Customer table. You really want to pull it from the inserted pseudo-table. Since you could have potentially multiple entries being inserted and you want to execute a stored procedure against each one, you'll need to (gasp!) loop through each entry in the Inserted table using the logic you have in your code. A few other things come to mind concerning why no emails are being sent:1) You are sending to the @PractitionerName. Is this an email address?2) Has the DBMail been configured and enabled on this server (Silly question, I know, but let's remove the obvious)3) As a test, can you hard code a known good email address into the trigger and confirm that the email sub-system is operational?
Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy |
|
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2014-05-29 : 12:07:10
|
| Thanks. Thought that it looked a bit off. Will test this. So far I guess that when the PractitionerName field is populated (this is an email address) then the dbo at the start of the trigger will be created? Executing just the script has not created anything so far. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2014-05-29 : 20:11:42
|
| From a design perspective this is not a great use of triggers. This kind of logic should be in the business layer. If there is a trigger at all, it should simply be to populate a queue of emails that need to be sent by some batch job or some such. I'd steer clear of triggers as a general rule, especially to do "work" rather than just modify other tables. |
|
|
|
|
|
|
|
|
Writing my first trigger