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

salma dodin 96 Reputation points
2021-12-11T12:01:56.03+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,256 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

Accepted answer
  1. salma dodin 96 Reputation points
    2021-12-11T14:11:14.347+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 98,811 Reputation points
    2021-12-11T13:04:43.487+00:00

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

    0 comments No comments