Making a rollback exempt insert

Question

Here's a weird question: Is there a way to make a rollback exempt insert into a table?

Here's the scenario: We have a trigger that does things.

Sometimes, this trigger will call RAISERRROR(). And the outer transaction rolls back.

However, in the trigger, I'd like to insert values into logging table and not have it go away during the rollback, a transaction exempt insert, if you will.

Answer 1

Writing this some time later. You can use an instead of trigger:

create table t
(
i int,
s varchar(10)
)
go

create table t2
(
i int,
s varchar(10)
)
go

create table tlog
(
i int,
s varchar(10)
)
go

alter trigger tt on t
INSTEAD OF INSERT
AS
BEGIN
    rollback transaction
    raiserror ('something went wrong', 16, 2)
    insert tlog (i,s)
    select i, s
    from inserted
END
go

truncate table t
truncate table t2
truncate table tlog
go

select * from t
select * from t2
select * from tlog
go

begin transaction
insert t2 (i,s) values (1, 'abc')
insert t (i,s) values (1, 'abc')
commit transaction
go

select * from t
select * from t2
select * from tlog

Gave the following output:

i s

---

(0 row(s) affected)

i s

---

(0 row(s) affected)

i s

---

(0 row(s) affected)

(1 row(s) affected) Msg 50000, Level 16, State 2, Procedure tt, Line 7 something went wrong

(1 row(s) affected) Msg 3609, Level 16, State 1, Line 4 The transaction ended in the trigger. The batch has been aborted. i s

---

(0 row(s) affected)

i s

---

(0 row(s) affected)

i s

---

1 abc

(1 row(s) affected)

Answer 2

banana catch

You can catch exceptions with try-catch and pass/throw them to the outer scope, where must exist the same stuff. This, by the way, allows you to collect call-stack. This solution can be implemented if you are working via storedprocs only. Every proc must have a pattern like this:

begin try
end try
begin catch
   if @@trancount > 0
     rollback

   insert into <log> (...)
   values (...)

   throw
end catch

So the topmost procedure will successfully insert a row into log table.

Cons:

• with deep hierarchy - too many "dummy" inserts to be rolled back
• still possible to log nothing if:
  • severity is too high
  • an SP does not conform this pattern
  • there is an outer/"client-side" transaction management

Pros:

• Not perfect but still feasible and not too hard to implement.

Feedback:

• I did use this approach on small project's production server, did collect call-stack and so on. It was a backoffice-software with ~50-100 online users, some number of robots running, far not "highload". It worked fine, helped to fix many issues. Overheads did not bother anyone.

elusive bandit

You can build a CLR-assembly that does insert into log table and nothing more... But! It's possible to specify a separate connection to make this assembly work with db via this separate connection. Which means - within a separate scope. So this assembly's method is being called from within a transaction however is being executed regardless this transaction scope.

So, instead of:

using(SqlConnection connection = new SqlConnection("context connection=true"))

just specify a regular connection string. After calling it - throw exception with modified ERROR_STATUS to avoid additional logging of same error.

begin try
end try
begin catch
   if @@trancount > 0
     rollback

   if @@ERRROR_STATUS != @done_with_logging
     exec asm.log(...)

   raiserror @err_msg, @severity, @done_with_logging
end catch

Cons:

• may result in connection pool issues
• a little longish way to gain a trivial insert
• may cause additional issues related to establishing a connection and the assembly itself (+permissions, ownership and so on)

Pros:

• the idea is slightly exciting
• can log to file instead of db

Feedback:

• I can't remember if I used this approach on prod (although I recall this as a more recent experience... or just an attempt to implement this for testing purposes)

knock, knock, admin

This simple statement (actually just the additional option WITH LOG) will write any error message you want to SQL SERVER event log:

RAISERROR(...) WITH LOG

This is not the way SQL SERVER log is should be used, but this is the quickest way to log something important (for issue resolution). Logged event can be viewed in SSMS agent's windows.

Cons:

• there can be permissions considerations
• poor search and zero customization capabilities
• dba is gonna hate you (if he ever examines server logs, of course)

Pros:

• single line implementation

Feedback:

• I've faced this way of logging at one company only. I guess, some time ago it was used by several developers to catch subtle bugs, but later got spread all around the code and became a "standard". So, some time later it became almost impossible to locate something particular in the event log... and actually only few coworkers had access to prod server's log. So it was literally a) harmful b) useless. So I got rid of it from managed part of system as soon as I found time for.

flying dutchman

{a place for opinion-based talks about building a system based on DML triggers}

It seems to me that you don't use stored procs in your project and execute ad hoc queries instead. If you have a backend application with ORM or something like that - write log with it. Moreover, perhaps this backend app is a better place to do things you do inside that trigger.

If your project is a client-server app without appserver/backend app and all you got is an ad hoc query and the trigger, then there is not much data to log. No call stack (on server side). And it will be hard to identify how did you (user, app) come to this particular exception. So logging on client-side might be more useful in this case.

Answer 3

If you create a table variable and then insert into this - This will not be included in the rollback so you can then dump the contents into a permanent table.

Eg

declare @tab table (msg varchar(255))

BEGIN TRY

    BEGIN TRANSACTION

    select 1+2

    INSERT @tab values ('first step complete')

    SElect 1/0

    INSERT @tab values ('2nd step complete')

    COMMIT

END TRY

BEGIN CATCH

    ROLLBACK

    SELECT * FROM @tab
END CATCH