Solved

Send Email on Insert

Posted on 2010-11-15
5
966 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
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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

920 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

16 Experts available now in Live!

Get 1:1 Help Now