AFTER INSERT/UPDATE/DELETE触发器仅在更新删除时触发，插入时不触发的原因

我为[dbo].[TriggerTable]表创建了名为[dbo].[EPRTrigger]的AFTER INSERT, UPDATE, DELETE触发器，发现该触发器仅在执行Update和Delete操作时触发，执行Insert操作时无响应。尝试将触发器定义中的AFTER替换为FOR，问题仍未解决。触发器代码如下：

CREATE TRIGGER [dbo].[EPRTrigger] 
ON [dbo].[TriggerTable]
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
    DECLARE @triggerType varchar(6), 
            @insert varchar(6) = 'Insert', 
            @update varchar(6) = 'Update', 
            @triggerTypeID int, 
            @delete varchar(6) = 'Delete'

    DECLARE @insertedHasRows bit, @deletedHasRows bit
    DECLARE @isInsert bit, @isUpdate bit, @isDelete bit

    SET @insertedHasRows = (SELECT CASE WHEN EXISTS (SELECT * FROM inserted) THEN 1 ELSE 0 END)
    SET @deletedHasRows = (SELECT CASE WHEN EXISTS (SELECT * FROM deleted) THEN 1 ELSE 0 END)
    SET @triggerType = 
        CASE 
            WHEN @insertedHasRows = 1 AND @deletedHasRows = 0 
                THEN @insert 
            WHEN @insertedHasRows = 1 AND @deletedHasRows = 1 
                THEN @update
            WHEN @insertedHasRows = 0 AND @deletedHasRows = 1 
                THEN @delete
        END

IF (@triggerType = @insert OR @triggerType = @update)
BEGIN
    -- take action
END
ELSE IF  (@triggerType = @delete)
BEGIN
    -- take action
END

问题排查方向 #

1. 验证触发器是否真正触发
   在触发器的AS块开头添加日志记录代码（需先创建TriggerLog表），执行Insert操作后查看日志，确认触发器是否执行：

   -- 先创建日志表
   CREATE TABLE TriggerLog (TriggerType VARCHAR(6), LogTime DATETIME DEFAULT GETDATE());
   
   -- 修改触发器，添加日志
   ALTER TRIGGER [dbo].[EPRTrigger] 
   ON [dbo].[TriggerTable]
   AFTER INSERT, UPDATE, DELETE
   NOT FOR REPLICATION
   AS
   BEGIN
       DECLARE @triggerType VARCHAR(6);
       SET @triggerType = 
           CASE 
               WHEN EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) THEN 'Insert'
               WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'Update'
               WHEN NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'Delete'
           END;
       -- 写入日志
       INSERT INTO TriggerLog (TriggerType) VALUES (@triggerType);
   
       -- 原有逻辑
       IF (@triggerType = 'Insert' OR @triggerType = 'Update')
       BEGIN
           -- take action
       END
       ELSE IF  (@triggerType = 'Delete')
       BEGIN
           -- take action
       END
   END
   

   执行Insert后查询SELECT * FROM TriggerLog，如果没有日志记录，说明触发器未触发，需检查：

   • 触发器是否被禁用：执行SELECT name, is_disabled FROM sys.triggers WHERE name = 'EPRTrigger'，若is_disabled=1，执行ENABLE TRIGGER [dbo].[EPRTrigger] ON [dbo].[TriggerTable]启用。
   • Insert操作是否成功：检查Insert语句是否有约束冲突（如主键重复、字段校验失败）导致回滚，回滚时触发器不会执行。

2. 检查分支内的操作逻辑
   如果日志显示@triggerType已赋值为'Insert'但操作未生效，说明-- take action处的代码存在问题：

   • 确认代码是否正确处理inserted表的数据（比如是否遗漏字段、逻辑判断错误）。
   • 检查是否有静默异常：在操作块添加错误捕获，比如：

     BEGIN TRY
         -- 原有操作代码
     END TRY
     BEGIN CATCH
         INSERT INTO TriggerLog (TriggerType) VALUES ('Insert_Error: ' + ERROR_MESSAGE());
     END CATCH
     

简化后的触发器逻辑（避免变量赋值风险） #

可以直接通过EXISTS判断操作类型，减少中间变量出错的可能：

CREATE TRIGGER [dbo].[EPRTrigger] 
ON [dbo].[TriggerTable]
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN
    -- Insert操作处理
    IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
    BEGIN
        -- 执行Insert相关操作
    END
    -- Update操作处理
    ELSE IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    BEGIN
        -- 执行Update相关操作
    END
    -- Delete操作处理
    ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    BEGIN
        -- 执行Delete相关操作
    END
END

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