发送带有触发器的电子邮件，当我收到电子邮件时，值错误 [英] Send an email with trigger, when I got the email the values were wrong

问题描述

我的问题与问题标题相同，下面是我尝试过的代码。

My question is same as in question heading and below is the code what I have tried.

ALTER TRIGGER [dbo].[Entrega_Insert]
ON [dbo].[Entrega]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DataEntrega DATETIME, @IdEncomenda INT, @QTDEncomenda INT
    DECLARE @IdVisita INT

    SELECT @DataEntrega = DataEntrega, @IdEncomenda = b.IdEncomenda
    FROM [dbo].[Entrega] AS a 
    INNER JOIN [dbo].[Encomenda] AS b ON a.[IdEncomenda] = b.[IdEncomenda]
    INNER JOIN [dbo].[Visita] AS c ON b.[IdVisita] = c.[IdVisita]
    --INNER JOIN 

    DECLARE @BigBody VARCHAR(500) = CAST(@DataEntrega AS VARCHAR(100)) + ' ' + CAST(@IdEncomenda AS VARCHAR(100))

    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Dc'
           ,@recipients = '[e-mail]'
           ,@subject = 'A sua encomenda foi processada e aceite.'
           ,@body = @BigBody
           ,@importance ='HIGH'
           ,@body_format='HTML'
END

推荐答案

渴望发表评论。

我强烈建议您不要来试图通过触发器发送电子邮件。您甚至不知道使用插入的事实表明您对SQL的工作方式还不熟悉。尝试发送电子邮件时，您将锁定表（或表的一部分）。

I would strongly discourage your from attempting to send email through a trigger. Just the fact that you don't even know to use inserted suggests that you are not familiar enough with how SQL works. You are going to be locking the table (or part of it) while the email is attempted.

您能做什么？最简单的方法是编写一个存储过程，以发送电子邮件并同时进行插入。

What can you do? The simplest is to write a stored procedure to send email and do the insert at the same time. This gives you more control over email failures.

更专业的解决方案可能涉及邮件队列。您将进行插入，然后将消息插入队列。在另一端，侦听器将发送电子邮件并处理电子邮件失败的任何问题。

The more "professional" solution would probably involve message queues. You would do the insert, then insert a message into a queue. At the other end, a listener would send the email and handle any issues with email failures.

这篇关于发送带有触发器的电子邮件，当我收到电子邮件时，值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！