SQL触发器仅执行一次问题求助：插入/更新触发邮件失效

嘿，我来帮你搞定这个触发器只触发一次的问题！你的需求是每次对table表执行插入/更新时都触发触发器，并且当变更记录的col1是1263、1276、1277时发送邮件。现在触发器只跑一次就没动静了，我梳理了几个核心排查点和修复方案：

1. 先检查触发器是否被禁用 #

有时候触发器执行时遇到严重错误（比如邮件发送失败），虽然SQL Server默认不会自动禁用触发器，但也有可能误操作把它关了。先跑这条命令看看状态：

SELECT name, is_disabled 
FROM sys.triggers 
WHERE name = 'emailonupdate';

如果is_disabled返回1，说明触发器被禁用了，执行这条命令重新启用：

ENABLE TRIGGER emailonupdate ON table;

2. 修复邮件内容拼接的bug #

你的代码里拼接@BODY的方式有问题：如果inserted表有多条符合条件的记录，变量赋值只会保留最后一条的内容；而且如果col2是数值类型，直接和字符串拼接会报错，导致触发器执行失败。

针对SQL Server 2017+版本（支持STRING_AGG）： #

DECLARE @BODY NVARCHAR(MAX);
SELECT @BODY = STRING_AGG(
    RTRIM(CONVERT(NVARCHAR(50), inserted.col1) + ' has added an item in db to ID# ' + CONVERT(NVARCHAR(50), inserted.col2)),
    CHAR(13) + CHAR(10)
) FROM inserted WHERE inserted.col1 IN (1263, 1276, 1277);

针对SQL Server 2016及更早版本： #

用FOR XML PATH的方式拼接内容：

DECLARE @BODY NVARCHAR(MAX);
SELECT @BODY = STUFF(
    (SELECT CHAR(13) + CHAR(10) + RTRIM(CONVERT(NVARCHAR(50), col1) + ' has added an item in db to ID# ' + CONVERT(NVARCHAR(50), col2))
     FROM inserted 
     WHERE col1 IN (1263, 1276, 1277)
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
);

3. 增加错误捕获，避免触发器中断 #

如果sp_send_dbmail调用失败（比如邮件配置错了、收件人无效），会直接抛出错误，导致触发器执行中断，看起来就像“没触发”。建议加个TRY...CATCH块捕获错误，还能把错误信息存起来方便排查：

ALTER TRIGGER emailonupdate ON table 
FOR INSERT, UPDATE 
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @BODY NVARCHAR(MAX);

    -- 用上面修复后的方式拼接邮件内容
    SELECT @BODY = STRING_AGG(
        RTRIM(CONVERT(NVARCHAR(50), inserted.col1) + ' has added an item in db to ID# ' + CONVERT(NVARCHAR(50), inserted.col2)),
        CHAR(13) + CHAR(10)
    ) FROM inserted WHERE inserted.col1 IN (1263, 1276, 1277);

    IF @BODY IS NOT NULL -- 有符合条件的记录才发邮件
    BEGIN
        BEGIN TRY
            EXEC msdb.dbo.sp_send_dbmail 
                @profile_name = 'DBA_Notifications', 
                @recipients = 'me@myemail.com', 
                @subject = 'Database Email', 
                @body = @body;
        END TRY
        BEGIN CATCH
            -- 可以自己建个TriggerErrorLog表来存错误信息
            INSERT INTO TriggerErrorLog (ErrorMessage, ErrorTime)
            VALUES (ERROR_MESSAGE(), GETDATE());
        END CATCH
    END
END

4. 验证触发器是否真的没触发 #

如果你怀疑触发器根本没执行，可以在触发器开头加个日志记录，看看每次操作是不是真的触发了：

ALTER TRIGGER emailonupdate ON table 
FOR INSERT, UPDATE 
AS 
BEGIN
    SET NOCOUNT ON;
    -- 先记录触发器触发日志，验证执行情况
    INSERT INTO TriggerExecutionLog (ExecutionTime, OperationType)
    VALUES (GETDATE(), CASE WHEN EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) THEN 'UPDATE' ELSE 'INSERT' END);

    -- 后续的邮件逻辑...
END

记得先自己创建TriggerExecutionLog表哦：

CREATE TABLE TriggerExecutionLog (
    ExecutionTime DATETIME DEFAULT GETDATE(),
    OperationType VARCHAR(10)
);

内容的提问来源于stack exchange，提问作者ernft