SQL Server DDL触发器架构变更时执行报错求助

当执行创建/修改/删除表等DDL操作时触发报错An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back，可从以下几个方面排查修复：

1. 确认SCHEMA_CHANGES表结构匹配 #

触发器核心逻辑是向dbo.SCHEMA_CHANGES插入审计数据，若表结构与插入值不匹配（如列缺失、数据类型不兼容、非空约束冲突），会直接导致插入失败并触发报错。

建议的表结构（若未创建需先执行） #

CREATE TABLE dbo.SCHEMA_CHANGES (
  [DateTime] DATETIME2 NOT NULL,
  [ServerName] NVARCHAR(128) NOT NULL,
  [ServiceName] NVARCHAR(128) NOT NULL,
  [SPID] INT NOT NULL,
  [SourceHostName] NVARCHAR(128) NOT NULL,
  [LoginName] NVARCHAR(128) NOT NULL,
  [UserName] NVARCHAR(128) NOT NULL,
  [SchemaName] NVARCHAR(128) NULL, -- 部分DDL事件无SchemaName
  [TABLE_NAME] NVARCHAR(128) NULL, -- 部分DDL事件无ObjectName
  [TargetObjectName] NVARCHAR(128) NULL,
  [EVENT_TYPE] NVARCHAR(128) NOT NULL,
  [ObjectType] NVARCHAR(128) NULL,
  [TargetObjectType] NVARCHAR(128) NULL,
  [EventData] XML NOT NULL,
  [COMMAND_TEXT] NVARCHAR(MAX) NULL, -- 部分DDL事件无CommandText
  [ReplicationAuditId] INT NOT NULL,
  [ReplicationOperation] NVARCHAR(128) NOT NULL,
  [ReplicationDateTime] DATETIME2 NOT NULL,
  [RowState] NVARCHAR(128) NOT NULL
);

2. 优化触发器错误处理逻辑 #

当前触发器的CATCH块仅清空@Eventdata，未有效抑制错误传播。修改后可确保即使插入审计数据失败，也不会中止原DDL操作：

修改后的触发器脚本 #

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [tr_SCHEMA_CHANGES]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
  SET NOCOUNT ON;
  IF OBJECT_ID('dbo.SCHEMA_CHANGES') IS NOT NULL
  BEGIN
    BEGIN TRY
      DECLARE @Eventdata XML;
      SET @Eventdata = EVENTDATA();
      INSERT dbo.SCHEMA_CHANGES (
       [DateTime]
      , [ServerName]
      , [ServiceName]
      , [SPID]
      , [SourceHostName]
      , [LoginName]
      , [UserName]
      , [SchemaName]
      , [TABLE_NAME]
      , [TargetObjectName]
      , [EVENT_TYPE]
      , [ObjectType]
      , [TargetObjectType]
      , [EventData]
      , [COMMAND_TEXT]
      , [ReplicationAuditId]
      , [ReplicationOperation]
      , [ReplicationDateTime]
      , [RowState]
            
      )
      VALUES (
       GETUTCDATE()
      , @@SERVERNAME
      , @@SERVICENAME
      , @Eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
      , HOST_NAME()
      , @Eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)')
      , @Eventdata.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)')
      , @Eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)')
      , @Eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
      , @Eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'nvarchar(128)')
      , @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
      , @Eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)')
      , @Eventdata.value('(/EVENT_INSTANCE/TargetObjectType)[1]', 'nvarchar(128)')
      , @Eventdata
      , @Eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(MAX)')
      , 0
      ,'Schema Replication'
      , GETUTCDATE()
      ,'queued'
      );
    END TRY
    BEGIN CATCH
      -- 可选：添加错误日志记录，例如写入专门的错误表
      -- INSERT INTO dbo.TriggerErrorLog (ErrorTime, ErrorMsg) VALUES (GETUTCDATE(), ERROR_MESSAGE());
      -- 不抛出错误，确保原DDL操作正常执行
    END CATCH
  END
END
GO

ENABLE TRIGGER [tr_SCHEMA_CHANGES] ON DATABASE
GO

3. 检查权限配置 #

触发器默认以执行DDL操作的用户身份运行，需确保该用户拥有INSERT权限到dbo.SCHEMA_CHANGES表。若无法给所有用户授权，可修改触发器使用高权限身份执行：

CREATE TRIGGER [tr_SCHEMA_CHANGES]
ON DATABASE
WITH EXECUTE AS 'sa' -- 替换为具有INSERT权限的用户
FOR DDL_DATABASE_LEVEL_EVENTS
AS
-- 触发器主体保持不变

4. 排查特定DDL事件的兼容性 #

部分DDL事件（如ALTER AUTHORIZATION）返回的EVENTDATA节点可能为空，需确保表中对应列允许NULL值。可通过单独测试单个DDL操作（如CREATE TABLE Test(ID INT);）并在CATCH块中记录错误信息，定位具体冲突点。

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