SQL服务器级DDL触发器：如何屏蔽事务中止提示消息？

这个错误是因为你的服务器级DDL触发器在执行过程中终止了事务，导致后续批处理被中止。以下是几种可行的解决办法：

1. 检查并移除触发器中的ROLLBACK语句 #

如果你的触发器代码里有ROLLBACK TRANSACTION语句，直接删掉它——你只需要备份对象，不需要阻止删除操作。移除后，触发器只会完成备份任务，不会干扰原DDL操作的事务，错误消息就会消失。

2. 在触发器中用独立事务执行备份操作 #

如果备份操作本身可能影响原事务，可以将备份逻辑放在独立的事务中执行，避免干扰DDL操作的事务上下文：

CREATE TRIGGER [trg_DDL_BackupOfRoutines]
ON ALL SERVER
FOR DROP_TRIGGER, DROP_VIEW, DROP_FUNCTION, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    -- 开启独立事务执行备份
    BEGIN TRANSACTION BackupTrans;
    BEGIN TRY
        -- 你的备份逻辑代码
        -- 比如获取EVENTDATA()、生成脚本、写入文件等操作
        
        COMMIT TRANSACTION BackupTrans;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION BackupTrans;
        -- 可选：记录备份失败的错误日志
    END CATCH
END;

3. 在执行DROP操作时添加错误捕获 #

如果不想修改触发器，可以在执行DROP操作的批处理中用TRY/CATCH块包裹，抑制错误消息：

BEGIN TRY
    -- 替换成你的DROP语句
    DROP PROCEDURE dbo.YourTargetProcedure;
END TRY
BEGIN CATCH
    -- 可以在这里记录错误，或者留空不处理
END CATCH

4. 异步执行备份操作 #

将备份逻辑放到SQL Server代理作业中，触发器只负责触发作业，不等待备份完成。这样触发器会快速结束，不会影响原DDL事务：

CREATE TRIGGER [trg_DDL_BackupOfRoutines]
ON ALL SERVER
FOR DROP_TRIGGER, DROP_VIEW, DROP_FUNCTION, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @ObjectName NVARCHAR(256) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)');
    DECLARE @ObjectType NVARCHAR(100) = @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)');
    
    -- 创建临时作业执行备份
    DECLARE @JobName NVARCHAR(128) = N'Backup_' + @ObjectName + N'_' + CONVERT(NVARCHAR(30), GETDATE(), 120);
    DECLARE @JobID UNIQUEIDENTIFIER;
    
    EXEC msdb.dbo.sp_add_job
        @job_name = @JobName,
        @job_id = @JobID OUTPUT;
    
    -- 添加作业步骤（替换成你的备份脚本）
    EXEC msdb.dbo.sp_add_jobstep
        @job_id = @JobID,
        @step_name = N'Backup Object',
        @subsystem = N'TSQL',
        @command = N'-- 这里写你的备份逻辑，比如生成脚本并保存到文件';
    
    -- 执行作业
    EXEC msdb.dbo.sp_start_job @job_id = @JobID;
    
    -- 可选：作业完成后自动删除
    EXEC msdb.dbo.sp_add_jobschedule
        @job_id = @JobID,
        @name = N'Cleanup Job',
        @freq_type = 64, -- 一次性执行
        @active_start_time = 0,
        @schedule_uid = NEWID();
END;

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