sp_send_dbmail from trigger with rollback - does not send

Vadim Rapp
Vadim Rapp used Ask the Experts™
on
I need to send an email from trigger that also performs rollback. But email is not sent

begin transaction
exec msdb..sp_send_dbmail .....  
rollback

Without rollback, the same statement sends it successfully and I receive the email. With rollback, there's message saying that it's queued, but I don't receive anything.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jason YousefSr. BI  Developer

Commented:
Hi, Because you're rolling back the send email statement, why you're using it like that?  what's the business need or what's the full trigger query?
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Can you issue the ROLLBACK before you send the email?
> what's the business need or what's the full trigger query?

To prohibit what shouldn't be happening (voiding an invoice), plus find out who tried it.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
If you want the email message (almost regardless) of the transaction, then do not include it within the transaction block.

Instead, set a flag to be checked which is initialised as if there was a failure, and set to success upon successful commit of the transaction. You can then "exec msdb..sp_send_dbmail ..... " with an appropriate "success" or "fail" or even change the recipients accordingly.

You can use XACT_STATE to check : https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-2017

The rollback will apply to pretty much everything (or at least attempt to rollback any db change) after the begin transaction up until the error condition invoking the rollback.

At the very least, move the "exec msdb..sp_send_dbmail ..... "  to after the rollback as Scott has said above.

Had a few unexpected experiences when trying to do the "right" thing, only to be foiled by the callousness of rollback. Worst part about that is timing of discovery, and so, all my unit tests now cover scenarios where rollback is activated.

Cheers,
Mark
Interestingly, when trying it from sql code like the above (plus raiserror statement), moving "exec dbmail" out of the transaction did help and everything worked as expected. But when the same trigger was triggered by the real application, email was not received unless I commented out raiserror. No idea why. So in the end, facing the choice of the ERP application not knowing that operation did not succeed, thus misleading the user (even wrongdoing user), or not sending the email, I eventually removed the rollback and raiserror from the trigger at all. Once finance manager knows who is voiding invoices, she will have to stop it by non-IT means.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Also depends on where you use the raiserror....

There's an interesting article which uses raiserror in the process of a transaction to invoke various error scenarios : https://sqlity.net/en/585/how-to-rollback-in-procedures/  it has been around for a while, and I have referred to it myself as a reminder on a few occasions.

It is possible to get a non fatal error (associates with any EXEC) : “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”

And a more recent article goes into detail with examples. Not quite a trigger situation, but similar in so much as it is being invoked as if it were an embedded SP.

https://www.mssqltips.com/sqlservertip/4897/handling-transactions-in-nested-sql-server-stored-procedures/

Read down to the partial transactions and maybe name the transaction within the trigger and treat it as a partial transaction....

Or, (as you have done), comment out rollback and raiserrors until you get a handle on who is voiding invoices - but at the risk of compromising database integrity ? Not sure I would be brave enough :)

In which case, maybe create your own audit / log and write to that as a separate exercise and run an asynchronous job over that table to generate the email alerts.

Difficult situation....

Cheers,
Mark
>  but at the risk of compromising database integrity ?

No, there's nothing wrong with it from the database standpoint, and it's being done by the user in the UI or the ERP application, not by some hack or anything. It's purely company's financial policy issue - "They should not be doing that!".

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial