Rolling back implicit Transactions in Try-Catch?

[jsteph]

Hi all,
I have a trigger which has a try/catch block. In the try block there is a write to a user-defined log file. What I need to do is allow the table update (on which the trigger is based) to commit, but I need the ability to bail on the log update--it's important but less so than the main table.

But if I do a rollback in the Catch block, it rolls back the main table update as well as the logfile update. Is it possible to do a Commit prior to the attempted logfile write in the try/catch block, even though there is no explicit Begin Tran?
--Jim

 

[markros]

If you would not do anything (no extra code in CATCH block), would it save the main record and if the error happened on log-file updating not save the log-file ?

 

[jsteph]

Markros,
No, I get a runtime error (using Access 2003 front-end with odbc linked tables) saying there's an uncommitable transaction and no logfile writes (not my user logfile, but the sql server transaction logfile) can be made.

I had looked up this error, an msdn site explained that it's because of the error, the trigger doesn't complete and the orignal table isn't updated.

However, I think I answered my question via testing this--I put an Explicit COMMIT in the trigger just before the try block, and when I force an error (for example, purposely writing 20 char to a 10 char field in the user logifle), it now works as I want--it doesn't give a runtime error, and the original table updates.

So it seems the answer is 'Yes', I can put an explicit commit in the trigger and then no matter what errors may occur subsequently in the trigger--the original table update happens.
Thanks,
--Jim

 

[markros]

I see, thanks for the info.