Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Send Email on Insert

Avatar of CCUITAdmin
CCUITAdmin asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
5 Comments1 Solution1055 ViewsLast Modified:
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