MSSQL Prevent rollback when trigger fails

I have an after insert/update/delete trigger, which inserts a new record in an AuditTable every time an insert/update/delete is made to a specific table. If the insertion in the AuditTable fails I'd like the first record to be inserted anyway and the error logged in a further table "AuditErrors".

This is what I have so far and I tried many different things but I can't get this to work if the trigger insert into the AuditTable fails (I test this by misspelling the name of a column in the AuditTable insert). NB: @sql is the insert into the 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 =开发者_JAVA百科 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

This is the only way I know of separating the original transaction from the trigger action. In this example the original insert completes even though the audit insert fails. Tested on 2008R2.

It's not pretty but it won't rollback the transaction!

It worked just fine with trusted authentication:

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

Drop the Audit table and it still completes the original transaction.

Cheers!

Instead of using sqlcmd, you may consider playing with BEGIN TRAN/ROLLBACK a little bit.

Note that, even tho a rollback command will undo every change made since the start of the statement which caused the trigger to fire, any changes made by subsequent commands will not.

All you have to do is to repeat the execution of the code in @sql if the transaction in which data is inserted in the audit table gets rolled back:

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