Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Send Email on Insert

Posted on 2010-11-15
5
Medium Priority
?
985 Views
Last Modified: 2012-05-10
Hello,

I am very new to triggers and I am having a problem. I apologize for a.) my bad code, b.) my lack of knowledge. Please assist with both! :) And thanks in advance for your time.

Apparently SQL Server doesn't perform a trigger for each row inserted, but (pardon my lack of correct technical verbiage) per "transaction" or "group" or something, If 3 rows get inserted all at the same time, then the trigger only goes off the last row. I need it check each row to see if it meets the criteria to send an email. I do know that cursors are terribly inefficient and should NEVER be used in a trigger, but I don't know how else to get it to trigger on each row.

Basically, I am trying to get it so that if a student signs up for a particular kind of course, it will send them an email about how to access the course info, etc. I have several variables to try and get the needed info for the email, I know that's also probably not the best way to do it.

I am on SQL Server 2008 SP1, Standard Ed.

In a nutshell:
How do I get a trigger to run for each row inserted? OR
How do I get the trigger to look at the group of transactions and send the correct email(s)? ALSO
Is there a better way to get the body of my email put together? It's kind of haphazard.

Let me know if you need any other info.

Here is my trigger code so far
------------------------------------
USE [test_db]
GO
/****** Object:  Trigger [dbo].[ccu_send_email_reg_online_cls]    Script Date: 11/12/2010 15:07:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[ccu_send_email_reg_online_cls]
ON [dbo].[STUDENT_CRS_HIST]

    AFTER INSERT on student_crs_hist for each row
        NOT FOR REPLICATION
        AS
--if statement to see if latest row is an online class        
if (select top 1 right(crs_cde, 2) from student_crs_hist order by job_time desc) = '99'
begin  

--declare all the needed variables
declare @@ClassName varchar(50) -- Full class name for email body
declare @@Body varchar(1000) -- Full email body
declare @@Subject varchar(50) -- Email Subject
declare @@email varchar(50) -- Student's Email address
declare @@idnum varchar(10) -- Student ID num
declare @@FirstName varchar(50) -- Student First name

declare @@year varchar(4) -- current year for last row in table
declare @@term varchar(2) -- current term for last row in table

declare @@ProfName varchar(50) -- Professor first + last name for email body
declare @@ProfEmail varchar(50) -- Professor email for email body
declare @@CourseCde varchar(20) -- Course code for class in question
 

set @@idnum = (select top 1 ID_NUM from STUDENT_CRS_HIST order by JOB_TIME desc)
set @@email = (select rtrim(addr_line_1) from ADDRESS_MASTER where addr_cde = '*eml' and ID_NUM = @@idnum)

set @@FirstName = (select isnull(preferred_name, first_name) from name_master where id_num = @@idnum)
set @@ClassName = (select top 1 CRS_TITLE from STUDENT_CRS_HIST order by JOB_TIME desc)
set @@CourseCde = (select top 1 CRS_CDE from STUDENT_CRS_HIST order by JOB_TIME desc)

set @@year = (select top 1 yr_cde from STUDENT_CRS_HIST order by JOB_TIME desc)
set @@term = (select top 1 trm_cde from STUDENT_CRS_HIST order by JOB_TIME desc)

set @@ProfName = (select first_name + ' ' + last_name from name_master where id_num in
(select lead_instructr_id from section_master where crs_cde = @@CourseCde and yr_cde = @@year and trm_cde = @@term))
set @@ProfEmail = (select email_address from name_master where id_num in
(select lead_instructr_id from section_master where crs_cde = @@CourseCde and yr_cde = @@year and trm_cde = @@term))

-- Set body to all of the content, variables, etc.
set @@Body = @@FirstName + ',' + CHAR(13) + CHAR(13) + 'Welcome to ' + rtrim(@@ClassName) +
'. This is an online course and all course participation will take place online ' +
'through the CCU Moodle website. To access your course, go to the CCU Moodle ' +
'website (http://moodle.ccuniversity.edu/) and log in using your CCU network ' +
'username and password. Once you have logged in, you will find your class on ' +
'the left side of the page under "My Courses."' + CHAR(13) + CHAR(13) +
'If you have any questions about your course, please contact your professor, ' + @@ProfName + ', ' +
'at ' + @@ProfEmail + '.' + CHAR(13) + CHAR(13) +

'If you have problems logging into Moodle or do not know your username and password,' +
' contact the IT Helpdesk at (513) 244-8647 or helpdesk@ccuniversity.edu.'


set @@Subject = 'Online Course Registration: ' + rtrim(@@ClassName)

--send actual email using aggregated data from above
EXEC msdb.dbo.sp_send_dbmail

@recipients= @@email,
@body = @@body,
@subject = @@Subject,

@profile_name ='OnlineReg'
end




0
Comment
Question by:CCUITAdmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:bclevlnd
ID: 34140466
You could use the output inserted.whatevercolumn(s) feature to put the data inserted into an email or a temp table to then send your emails.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 34143283
>> If 3 rows get inserted all at the same time, then the trigger only goes off the last row.

I have to disagree with the above statement.
A small correction, Trigger would be fired three times if 3 rows are inserted at a time and might be there is some other issues with your Exchange or email server.

>> How do I get a trigger to run for each row inserted?

As I mentioned, trigger would be fired as many times the no. of records inserted are high.

>> How do I get the trigger to look at the group of transactions and send the correct email(s)? ALSO
Is there a better way to get the body of my email put together? It's kind of haphazard.

Try the Modified code below
ALTER TRIGGER [dbo].[ccu_send_email_reg_online_cls]
ON [dbo].[STUDENT_CRS_HIST]
AFTER INSERT
NOT FOR REPLICATION
AS

--declare all the needed variables
declare @@ClassName varchar(50) -- Full class name for email body
declare @@Body varchar(1000) -- Full email body
declare @@Subject varchar(50) -- Email Subject
declare @@email varchar(50) -- Student's Email address
declare @@idnum varchar(10) -- Student ID num
declare @@FirstName varchar(50) -- Student First name

declare @@year varchar(4) -- current year for last row in table
declare @@term varchar(2) -- current term for last row in table
declare @@crs_cde varchar(2)

declare @@ProfName varchar(50) -- Professor first + last name for email body
declare @@ProfEmail varchar(50) -- Professor email for email body
declare @@CourseCde varchar(20) -- Course code for class in question
 
SELECT @@idnum = ID_NUM, 
       @@ClassName = CRS_TITLE,
       @@CourseCde = CRS_CDE,
       @@year = yr_cde,
       @@term = trm_cde,
       @@crs_cde = Right(crs_cde, 2)
FROM INSERTED

set @@email = (select rtrim(addr_line_1) from ADDRESS_MASTER where addr_cde = '*eml' and ID_NUM = @@idnum)
set @@FirstName = (select isnull(preferred_name, first_name) from name_master where id_num = @@idnum)


--if statement to see if latest row is an online class        
if @@crs_cde = '99'
begin  

set @@ProfName = (select first_name + ' ' + last_name from name_master where id_num in
(select lead_instructr_id from section_master where crs_cde = @@CourseCde and yr_cde = @@year and trm_cde = @@term))
set @@ProfEmail = (select email_address from name_master where id_num in
(select lead_instructr_id from section_master where crs_cde = @@CourseCde and yr_cde = @@year and trm_cde = @@term))

-- Set body to all of the content, variables, etc.
set @@Body = @@FirstName + ',' + CHAR(13) + CHAR(13) + 'Welcome to ' + rtrim(@@ClassName) +
'. This is an online course and all course participation will take place online ' +
'through the CCU Moodle website. To access your course, go to the CCU Moodle ' +
'website (http://moodle.ccuniversity.edu/) and log in using your CCU network ' +
'username and password. Once you have logged in, you will find your class on ' +
'the left side of the page under "My Courses."' + CHAR(13) + CHAR(13) +
'If you have any questions about your course, please contact your professor, ' + @@ProfName + ', ' +
'at ' + @@ProfEmail + '.' + CHAR(13) + CHAR(13) +

'If you have problems logging into Moodle or do not know your username and password,' +
' contact the IT Helpdesk at (513) 244-8647 begin_of_the_skype_highlighting            (513) 244-8647      end_of_the_skype_highlighting or helpdesk@ccuniversity.edu.'


set @@Subject = 'Online Course Registration: ' + rtrim(@@ClassName)

--send actual email using aggregated data from above
EXEC msdb.dbo.sp_send_dbmail

@recipients= @@email,
@body = @@body,
@subject = @@Subject,

@profile_name ='OnlineReg'
end
GO

Open in new window

0
 

Author Comment

by:CCUITAdmin
ID: 34147273
Wow. I kept reading about the "inserted" table, but nothing I would try would work. I realize now it's because the "inserted" table only exists within the context of the trigger firing. I was trying to test it by just running the "meat" of the trigger code without actually writing it back to the trigger and then activating the trigger by inserting a row into the table.

I am still confused, though, because I read several articles about triggers while I was trying to learn and all of them said something to the effect of "unlike in Oracle, SQL Server triggers only fire once per DML and not once per row inserted."

You get all the points for the solution, though, because I just tested it several times and it behaved exactly as it was supposed to.

Thank you so much for the help!
0
 

Author Closing Comment

by:CCUITAdmin
ID: 34147279
You were awesome!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34151664
>> "unlike in Oracle, SQL Server triggers only fire once per DML and not once per row inserted."

Actually its wrong and you might have tested and realized by yourself as of now..
And glad to help you out.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question