CODEWITHSUNDEEP
sql-servertriggers
Dean
Dean

Reputation: 159

Writing a SQL trigger for the first time

I am trying to write a SQL trigger for the first time but running into a problem.

I have a database called mytest and within that is a table called customer. When someone registers a customer record is created. On the signup form that creates the customer record there is an option to add a doctors name.

What I am trying to do within the trigger is send the doctor an email with a simple message "This customer says you are their doctor, please can you reply to this email stating yes or No". My main option is to automate this part of the procedure to update the SQL table once I get this part working!

Here is what I have written so far

CREATE TRIGGER dbo.SEND_MAIL_TO_PRACTITIONER 
ON  dbo.mytest 
AFTER INSERT
AS 
BEGIN

DECLARE @PractitionerName VARCHAR(100)
DECLARE @body VARCHAR(100)

SET @PractitionerName=(SELECT PractitionerName FROM customer )
SET @body=(SELECT customername FROM customer)+'  emailed us saying you were his doctor, please can you confirm yes or no'


IF @PractitionerName IS NOT NULL
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
      @recipients = @PractitionerName,           
      @subject = 'TEST', 
      @body = @body;
END
END
GO

The SQL executes but no emails are being sent. I'd also like to change customername to a combination of FirstName and LastName fields.

Anybody help point me in the right direction?

Upvotes: 1

Views: 83

Answers (1)

vhadalgi
vhadalgi

Reputation: 7189

You need to start the mail service first in SQL server consider sending a test mail.. If it fails check the mail log by right click in db mail option!

Configure as the doc

http://msdn.microsoft.com/en-IN/library/ms190307.aspx

http://technet.microsoft.com/en-us/library/ms176087.aspx

Upvotes: 1

Related Questions