sql-server - SQL 服务器 : don't send email if transaction rolls back

Question

我有一段代码使用 TRY/CATCH 回滚事务，如果 INSERT失败。但是，在代码的中间，我有一段代码在每个循环中发送一封电子邮件。

我遇到的问题是循环发送电子邮件然后插入。所以假设在 9 个进程/轮之后，第 10 轮失败 INSERT ;然后整个事务回滚，但 9 封邮件已经发送。我该如何改进它以便不先发送电子邮件？或者如果交易失败，如何避免发送任何电子邮件？

这是我的代码的简化版本，用于测试目的。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[something]
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON; 

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRY
        DECLARE @something INT

        DECLARE ROW_RES CURSOR FOR
            SELECT something 
            FROM something

        OPEN ROW_RES

        FETCH NEXT FROM ROW_RES INTO @something

        WHILE (@@fetch_status = 0) 
        BEGIN
            IF (@something > 0)
            BEGIN
                EXEC msdb.dbo.sp_send_dbmail
                     @profile_name = 'myprofile',
                     @from_address = 'do_not_reply@me.com',
                     @recipients = 'me@me.com',
                     @copy_recipients = 'ccme@me.com',
                     @subject = @email_subject,
                     @body = @email_body,
                     @body_format = 'html'
            END

            FETCH NEXT FROM ROW_RES INTO @something
        END

        IF (@something = false )
        BEGIN
            UPDATE something 
            SET something = (@something) 
            WHERE something = @something
        END
        ELSE IF (@something = true)
        BEGIN
            INSERT INTO something (something) 
            VALUES (@something)
        END

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        DECLARE @MESSAGE NVARCHAR(2000), @SEVERITY INT, @STATE INT
        --See if there is a hanging transaction
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        --Rethrow the error to the calling application      
        SET @MESSAGE = ERROR_MESSAGE() 
        SET @SEVERITY = ERROR_SEVERITY() 
        SET @STATE = ERROR_STATE()
        RAISERROR(@MESSAGE, @SEVERITY, @STATE)      
    END CATCH

    SET NOCOUNT OFF;
END
CLOSE ROW_RES
DEALLOCATE ROW_RES

Answer 1

而不是调用 sp_send_dbmail在游标内，您可以将所有需要的元数据插入到队列表中，在游标完成后，如果没有问题，您可以运行另一个游标或循环，只为队列表中的每一行发送电子邮件.