Getting this error: Msg 3609, Level 16, State 1, Line 88 The transaction ended in the trigger. The batch has been aborted.

Question

So I'm trying an exercise Trigger Code that would send an email if someone tries to insert into the adventure database a special offer that is equal to or greater than 80%, My Code goes Like this:

-----------------------
The Trigger-------------------------------

CREATE TRIGGER special_offer_6
ON [Sales].[SpecialOffer]
AFTER INSERT
AS
EXEC msdb.dbo.sp_send_dbmail
@Anton = 'DB ADMIN',
@recipients = 'salma.dodin@Stuff .com',
@Tomas Podoba = ' Oh no...sombody just got more than an 80% discount!!!',
@subject = 'Warning'
INSERT INTO [Sales].[SpecialOffer]
( [Description],
[DiscountPct],
[Type],
[Category],
[StartDate],
[EndDate],
[MinQty],
[MaxQty])
SELECT [Description],
[DiscountPct],
[Type],
[Category],
[StartDate],
[EndDate],
[MinQty],
[MaxQty] FROM inserted
WHERE [DiscountPct] >= 0.80

and then the Insert Code:

-------------------------
Insert test-----------------------------

SELECT * FROM [Sales].[SpecialOffer]  
 INSERT INTO [Sales].[SpecialOffer]  
 ([Description],  
 [DiscountPct],  
 [Type],  
 [Category],  
 [StartDate],  
 [EndDate],  
 [MinQty],  
 [MaxQty])  
  VALUES   
  ('Special Offer' , '0.80', 'special discount','customer', '2005-06-01 00:00:00.000','2008-12-31 00:00:00.000', '15', '40')  

I have Configured the email database using these codes:

--------------------
email database configuration---------------

EXEC sp_configure 'show advanced options', '1';  
RECONFIGURE  
GO  
EXEC sp_configure 'Database Mail XPs', 1;  
RECONFIGURE  
GO  
EXECUTE msdb.dbo.sysmail_add_account_sp    
    @account_name = 'DB ADMIN',    
    @description = 'Mail account for admin emails.',    
    @email_address = 'salma.dodin@gmail.com',    
    @replyto_address = 'salma.dodin@gmail.com',    
    @display_name = 'DB Automated Mailer',    
    @mailserver_name = 'smtp.example.com',  
    @port = 25;    
  
EXECUTE msdb.dbo.sysmail_add_profile_sp    
    @profile_name = 'DB ADMIN Profile',    
    @description = 'Profile for admin emails.';  
  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp    
    @profile_name = 'DB ADMIN Profile',    
    @account_name = 'DB ADMIN',    
    @sequence_number = 1;  

Yet, when I try and execute the insert test code I get this message:

Mail (Id: 4) queued.
Msg 3609, Level 16, State 1, Line 88
The transaction ended in the trigger. The batch has been aborted.

any help please?

Answer 1

Thank you so much for your answer, actually yes there was a rollback transaction coded into the trigger and I had removed it during the many tests I've been running.
yes I'm still learning so whatever exercise I find I try my hands around it. thank you for the fix on the trigger code itself I was struggling with that one. I'll go with your advise and not use the database mail, I agree in real world a report would be much more efficient.

Answer 2

Is that really the trigger code? That trigger will always send a mail, no matter what data that was inserted. And then the trigger would insert the rows with a high discount a second time into the table. That looks funny, and does not agree with your description. I would expect something like:

IF EXISTS (SELECT * FROM inserted WHERE DiscountPct > 0.80)
BEGIN 
    EXEC sp_send_dbmail ...
    ...
END
go

And no INSERT statement.

Normally, when you get that error, this is because you have a ROLLBACK TRANSACTION statement in the trigger. I don't see it here, but since your trigger code looks so unrealistic, I am not sure that I should trust it. (Having answered questions in forums for many years, I have learnt the hard way that posters often withhold essential information.)

I also like to point out that it's doubtful that this is a good idea in the first place. It's not uncommon to see people to ask for how to send mail if a certain value is inserted or similar, but I often ask myself, if that meets their business needs. It can easily result in someone being spammed and just ignoring the mail. In many cases it is better to produce a report a that permits persons in charge to monitor suspect values. Not the least could such a report permit the user to set the filter at different levels when looking for suspect patterns. A hard-coded limit at 80% gets too black and white.

I assume that since you are working in AdventureWorks that you are just playing around. Personally, I see no reason to be in a hurry to learn to use Database Mail. It's not really a core feature. (I worked with SQL Server for many years without using it. In the system I work with now, we do use Database Mail, but it's mainly for daily monitoring, and we are not doing it from triggers.)