sp_send_dbmail from trigger with rollback - does not send

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.
LVL 40
Vadim RappAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason YousefSr. BI  DeveloperCommented:
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?
0
Scott PletcherSenior DBACommented:
Can you issue the ROLLBACK before you send the email?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vadim RappAuthor Commented:
> 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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
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
0
Vadim RappAuthor Commented:
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.
0
Mark WillsTopic AdvisorCommented:
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
0
Vadim RappAuthor Commented:
>  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!".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.