Reputation: 745
I created my very first MSSQL trigger in MSSQL SERVER 2008 following the following guide; https://web.archive.org/web/20210306173842/https://www.4guysfromrolla.com/webtech/tips/t051706-1.shtml
I have setup mail in the SQL Server Management Studio and test mails run successfully, but I never get the email from my trigger. Two questions;
1) Is my trigger valid? 2) Where does MSSQL Server log when a trigger is executed?
CREATE TRIGGER updTicketHeader
on TicketHeader
FOR UPDATE
AS
IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN
declare @body varchar(3000)
declare @TicketNumber int
declare @originatorFirst varchar(64)
declare @originatorLast varchar(64)
declare @originatorEmail varchar(64)
declare @ticketSubject varchar(16)
declare @priority int
declare @ticketDescription varchar(128)
SELECT @TicketNumber = ticketNumber,
@originatorFirst = originatorFirst,
@originatorLast = originatorLast,
@originatorEmail = originatorEMail,
@ticketSubject = ticketSubject,
@priority = priority,
@ticketDescription = ticketDescription
FROM inserted
SET @body =
'New ticket ' + @TicketNumber + ' has been created!
First Name: ' + @originatorFirst + '
Last Name: ' + @originatorLast + '
Email: ' + @originatorEmail + '
Priority: ' + @priority + '
Ticket Subject: ' + @ticketSubject + '
Ticket Description: ' + @ticketDescription
EXEC master..xp_sendmail
@recipients = '[email protected]',
@subject = 'New Ticket ' + @ticketNumber + ' has been created!',
@message = @body
END
GO
Thanks to Code Magician for the help!
My trigger is now working with the following code;
CREATE TRIGGER updTicketHeader
on TicketHeader
FOR INSERT
AS
IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN
declare @body varchar(3000)
declare @TicketNumber varchar(8)
declare @originatorFirst varchar(64)
declare @originatorLast varchar(64)
declare @originatorEmail varchar(64)
declare @ticketSubject varchar(16)
declare @priority varchar(8)
declare @ticketDescription varchar(128)
SELECT @TicketNumber = ticketNumber,
@originatorFirst = originatorFirst,
@originatorLast = originatorLast,
@originatorEmail = originatorEMail,
@ticketSubject = ticketSubject,
@priority = priority,
@ticketDescription = ticketDescription
FROM inserted
SET @body =
'New ticket ' + @TicketNumber + ' has been created!
First Name: ' + @originatorFirst + '
Last Name: ' + @originatorLast + '
Email: ' + @originatorEmail + '
Priority: ' + @priority + '
Ticket Subject: ' + @ticketSubject + '
Ticket Description: ' + @ticketDescription
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@profile_name = 'Default',
@subject = 'New Ticket has been created!',
@body = @body
END
GO
Upvotes: 1
Views: 86
Reputation: 24032
Woah. Why is your trigger FOR UPDATE
Yet your email body is talking about a new ticket being created? Consider using FOR INSERT
Just in case that's not the only issue, everything below is still worth a read.
First it's entirely possible that your trigger is firing but not doing anything. never assume your update/insert/delete is only one record.
I see your code is checking for a single row affected but if there are two rows updated at the same time, nothing will happen. It would probably be better to code a loop if you want email to be send for every update.
SQL doesn't log that a trigger is firing, Triggers are as transactional as the SQL that triggers them. In other words, if your update worked, your trigger worked. In this case, either you are doing a multi-row update that your trigger is skipping or you've not configured xp_sendmail
correctly. I suspect the latter.
I would recommend against using xp_sendmail
. For starters, it's deprecated now. It's also a hassle to configure, it requires installing a MAPI client (like outlook) on your database server.
The preferred way to send email from the database these days is using sp_dbmail
. Rather than SQL server sending commands to a local install of outlook express, sp_dbmail
will simply use SMTP.
Also, sp_dbmail will log your emails that are sent. It keeps copies of outgoing e-mail messages and stores them in the following tables:
sysmail_allitems
, sysmail_sentitems
, sysmail_unsentitems
, sysmail_faileditems
.
It becomes much easier to troubleshoot your actual issue. Using sp_dbmail
Sent item statuscan be seen in sysmail_mailitems table. if sent_status
is 1 then it sent successfully, failures are 2 and 3 is unsent.
Anyway, read all about dbmail here
And here's a video describing how to configure DB mail in SQL 2008
Upvotes: 1