Solved

Send Email on Insert

Posted on 2010-11-15
5
962 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
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:bclevlnd
Comment Utility
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 500 total points
Comment Utility
>> 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
Comment Utility
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
Comment Utility
You were awesome!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> "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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now