CODEWITHSUNDEEP
sql-servertriggers
CryptoJones
CryptoJones

Reputation: 745

Is my trigger executing?

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

Answers (1)

Code Magician
Code Magician

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

Related Questions