MSSQL防止触发器失败时回滚

Question

我有一个插入/更新/删除后触发器，每次对特定表进行插入/更新/删除时，它都会在 AuditTable 中插入一条新记录。如果 AuditTable 中的插入失败，我希望无论如何都插入第一条记录，并将错误记录在另一个表“AuditErrors”中。

这就是我到目前为止所做的，我尝试了很多不同的事情，但如果触发器插入 AuditTable 失败，我就无法让它工作（我通过错误拼写 AuditTable 插入中的列名称来测试这一点）。注意：@sql 是插入到 AuditTable 中。

DECLARE @TranCounter INT
SET @TranCounter = @@TRANCOUNT
IF @TranCounter > 0
  SAVE TRANSACTION AuditInsert;
ELSE
  BEGIN TRANSACTION;
BEGIN TRY
  EXEC (@sql)
  IF @TranCounter = 0
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  -- roll back
  IF @TranCounter = 0
    ROLLBACK TRANSACTION;
  ELSE
    IF XACT_STATE() <> -1
      ROLLBACK TRANSACTION AuditInsert;
  -- insert error into database    
  IF @TranCounter > 0
    SAVE TRANSACTION AuditInsert;
  ELSE
    BEGIN TRANSACTION;
  BEGIN TRY
    INSERT INTO [dbo].[AuditErrors] ([AuditErrorCode], [AuditErrorMsg]) VALUES (ERROR_NUMBER(), ERROR_MESSAGE())
    IF @TranCounter = 0
      COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    -- roll back
    IF @TranCounter = 0
      ROLLBACK TRANSACTION;
    ELSE
      IF XACT_STATE() <> -1
        ROLLBACK TRANSACTION AuditInsert;
  END CATCH
END CATCH

Answer 1

这是我所知道的将原始交易与触发操作分开的唯一方法。在此示例中，即使审核插入失败，原始插入也会完成。在 2008R2 上测试。

虽然不太漂亮，但它不会回滚事务！

它与受信任的身份验证一起工作得很好：

create table TestTable(
    ID int identity(1,1) not null
    ,Info varchar(50) not null
    )
GO
create table AuditTable(
    AuditID int identity(1,1) not null
    ,TestTableID int not null
    ,Info varchar(10) -- The failure is the mismatch in length
)
GO

create procedure insertAudit @id int, @Info varchar(50)
as
set nocount on;
begin try
    insert into AuditTable(TestTableID,Info)
    values(@id,@Info);
end try
begin catch
    select 0
end catch;
GO

create trigger trg_TestTable on TestTable
AFTER INSERT
as
begin
set nocount on;

declare @id int,
        @info varchar(50),
        @cmd varchar(500),
        @rc int;
select @id=ID,@info=Info from inserted;

select @cmd = 'osql -S '+@@SERVERNAME+' -E -d '+DB_NAME()+' -Q "exec insertAudit @id='+cast(@id as varchar(20))+',@Info='''+@info+'''"';

    begin try
        exec @rc=sys.xp_cmdshell @cmd
        select @rc;
    end try
    begin catch
        select 0;
    end catch;
end
GO

删除审计表，它仍然完成原始事务。

干杯!

Answer 2

您可以考虑使用 BEGIN TRAN/ROLLBACK 来代替使用 sqlcmd。

请注意，即使回滚命令将撤消自导致触发器触发的语句开始以来所做的所有更改，后续命令所做的任何更改都不会。

如果审计表中插入数据的事务回滚，您所要做的就是重复执行@sql中的代码：

TRIGGER BEGINS

<INSERT INSERTED AND DELETED TABLES INTO TABLE VARIABLES, U'LL NEED THEM>

BEGIN TRY

BEGIN TRAN

INSERT INTO AUDITTABLE SELECT * FROM @INSERTED

COMMIT

END TRY

BEGIN CATCH

ROLLBACK

REDO ORIGINAL INSERT/UPDATE/DELETE USING TRIGGER TABLE VARIABLES (@INSERTED AND @DELETED)

INSERT INTO AUDITERROS...

END CATCH

BEGIN TRAN -- THIS IS TO FOOL SQL INTO THINKING THERE'S STILL A TRANSACTION OPEN

TRIGGER ENDS