Avatar of CCUITAdmin
CCUITAdmin asked on

Send Email on Insert

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




Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
bclevlnd

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.
ASKER CERTIFIED SOLUTION
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
CCUITAdmin

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!
ASKER
CCUITAdmin

You were awesome!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Raja Jegan R

>> "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.