TRIGGER and ERROR LOGGING

Question

Post reply

TRIGGER and ERROR LOGGING

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
October 6, 2010 at 8:57 am

#227592

Hi,
I have a trigger that works well. But I do want errors logged.
For starters, I added these lines to the bottom of the trigger:
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
EXECUTE sp_cl_GetErrorInfo;
END CATCH
When I run these lines by itself, it works well; sp_cl_GetErrorInfo is a SP that logs error number etc. It does log a dibvide by zero error.
But when I let the trigger fire, I get a message:
---------------------------
Microsoft SQL Server Management Studio
---------------------------
No row was updated.
The data in row 134 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The statement has been terminated.
Why is this? I also get this message when I delete the select 1/0 line, and put the begin try / end try around the actual trigger
Thanks
Ray

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Geoff A SSChampion Points: 11511 More actions · Answer 1

Since you are calling a SP inside the trigger all errors needs to be rolled back before you perform any writes.

try adding IF XACT_STATE() = -1 ROLLBACK to your trigger.

BEGIN CATCH

IF XACT_STATE() = -1 ROLLBACK

EXECUTE sp_cl_GetErrorInfo;

END CATCH

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 2

Thanks for your answer.

I tried it, but now I get:

---------------------------

Microsoft SQL Server Management Studio

---------------------------

No row was updated.

The data in row 134 was not committed.

Error Source: .Net SqlClient Data Provider.

Error Message: The transaction ended in the trigger. The batch has been aborted.

Correct the errors and retry or press ESC to cancel the change(s).

It is quite a lengthy trigger, in which updates in other tables occurs... can that have something to do with it?

Thanks,

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 3

I've tried several things, with no result.

I am curious, am I asking something stupid? All I want, as DBA, that when a trigger fails, the faillure gets logged so I get alerted and can fix it. I hope that is not too much to ask for.....