Send Email in SQL through trigger

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Dear,

i created a trigger on CustomerCall when call inserted & Assign to particular user i want to send email.

Below is the working trigger only how to get the Assign user email address from User Table

User-Table
----------
us_No
Us_Name
Us_Password
Us_Email

I want when the customer call assign to particular user take that email address and put it in below trigger.

SQL

alter TRIGGER CallInserted
ON [dbo].[CustomerCall]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM inserted)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
	// here how to get receipent email from User table
          @recipients = 'recipients', 
          @profile_name = 'SendEmailCM',
          @subject = 'Notification', 
          @body_format = 'html',
          @body = 'body'
    END
END
GO

Posted 15-Aug-15 22:38pm

basitsar

Updated 15-Aug-15 23:26pm

Wendelius

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-08-15T23:25:00

Solution 1

Taken that the CustomerCall table has a reference column us_No pointing to User table then you can fetch the email address by joining the inserted and the user.

In other words, something like:

SQL

alter TRIGGER CallInserted
ON [dbo].[CustomerCall]
FOR INSERT
AS
BEGIN
   DECLARE @us_email varchar(100);
   SET NOCOUNT ON;

   IF EXISTS (SELECT * FROM inserted)
   BEGIN
      SELECT @us_email = us_email
      FROM inserted i,
           user u
      WHERE u.us_no = i.us_no;

      EXEC msdb.dbo.sp_send_dbmail
           @recipients = @us_email,
           @profile_name = 'SendEmailCM',
           @subject = 'Notification',
           @body_format = 'html',
           @body = 'body'
   END
END
GO

However, notice that this fetches only one email address. If multiple rows are inserted in a single batch this trigger would fail because inserted would contain several rows.

Because of this I would suggest using a cursor to loop through the inserted records if separate emails should be sent based on each insertion.

For more information about cursors, see DECLARE CURSOR (Transact-SQL)[^]

Posted 15-Aug-15 23:25pm

Wendelius

Comments

basitsar 17-Aug-15 1:21am

Thanks A lot dear, other thing what i want in Customercall if Assignto column is null then not execute email code how to do this. and if another column status='Closed' then send email to another persone from User table. Email Column is lineMangerEmail

Below is the example.

If (AssignTo=null) then

exit
end if

if (Status='Closed') then
send email to LineManager column from User table
and execute email
end if

thanks
Basit.

Wendelius 17-Aug-15 1:50am

If these are columns in the CustomerCall table, just fetch the new values from the inserted and add the necessary logic to the trigger. The query could be something like

SELECT @us_email = u.us_email,
@status = i.status,
@assignto = i.assignto
FROM inserted i,
user u
WHERE u.us_no = i.us_no;

Just define the variables needed as their corresponding data types in the table.

Also note that the same comment still applies, if several rows are inserted in the same batch, this will not work correctly.

basitsar 17-Aug-15 3:10am

Dear Thanks A lot. I have edited trigger.

Below is the Trigger

ALTER TRIGGER [dbo].[CallInserted]
ON [CM].[dbo].[CustomersCalls]
FOR update
AS
BEGIN

DECLARE @us_email varchar(500);
DECLARE @CallNo varchar(500);
DECLARE @CallNo2 varchar(500);
DECLARE @CallComplaint varchar(1000);

DECLARE @LineMgrEmail varchar(500);
DECLARE @Status varchar(500);
SET NOCOUNT ON;

IF EXISTS (SELECT * FROM inserted)
BEGIN
SELECT @us_email = EmailAddress
FROM inserted i,
Users u
WHERE u.UserNo = i.AssignedBy;

SELECT @LineMgrEmail = EmailLineManager
FROM inserted i,
Users u
WHERE u.UserNo = i.AssignedBy;

SELECT @CallNo ='Notification :Call Number is :' + SPACE(1)+ CallNumber
FROM inserted
SELECT @CallNo2 ='Notification :Call Closed, Call Number is :' + SPACE(1)+ CallNumber
FROM inserted

SELECT @Status =[Status]
FROM inserted

SELECT @CallComplaint = Complaint
FROM inserted

EXEC msdb.dbo.sp_send_dbmail
@recipients = @us_email,
@profile_name = 'SendEmailCM2',
@subject = @CallNo,
@body_format = 'html',
@body = @CallComplaint

END
END

What i want is if @Status=1 then
EXEC msdb.dbo.sp_send_dbmail
@recipients = @us_email,
@profile_name = 'SendEmailCM2',
@subject = @CallNo,
@body_format = 'html',
@body = @CallComplaint
and if @Status=2

EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailLineManager,
@profile_name = 'SendEmailCM2',
@subject = @CallNo2,
@body_format = 'html',
@body = @CallComplaint

How to use CASE or If in between.
Please help on this

Thanks
Basit.

Wendelius 17-Aug-15 3:27am

Not sure if I understand the question correctly, but you can use simply

IF @Status = 1 BEGIN
EXEC msdb.dbo.sp_send_dbmail
...
END
IF @Status = 2 BEGIN
EXEC msdb.dbo.sp_send_dbmail
...
END

basitsar 17-Aug-15 3:55am

Many Many Thanks. The problem has been solved.

Wendelius 17-Aug-15 4:02am

Glad it helped :)