Send email on trigger failure

Question

I have successfully configured and able to sent email using 'Database mail' on SQL server 2008 R2 by calling msdb.dbo.sp_send_dbmail stored procedure. And I can catch the failure of a query in a trigger by putting it in try-catch block.

But What I need to do is, Send an email when a trigger fail to execute a query. But I can not execute above stored procedure within the catch block. It gave me following error.

Msg 3930, Level 16, State 1, Procedure sp_send_dbmail, Line 64
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.

Is there any other way to send email on trigger failure? Or how to fix this?

Answer 1

Trigger failure will always cause a transaction rollback and as such it cannot do any transactional operation, like calling sp_send_dbmail. One trick is to leverage custom profiler trace events, since they are sent on a standalone transaction:

create queue q;
create service sq 
    on queue q
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification trace_user_0
    on server 
    for TRC_USER_CONFIGURABLE
    to service N'sq', N'current database';
go


create procedure usp_sq
as
begin

    declare @dh uniqueidentifier, 
        @mt sysname, 
        @mb varbinary(max),
        @mx xml;

    begin transaction

    receive top(1) 
        @dh = conversation_handle,
        @mt = message_type_name,
        @mb = message_body
        from q;

    if @mt = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
    begin
        set @mx = cast(@mb as xml);

        declare @eventText nvarchar(4000);
        select @eventText = @mx.value (N'(//EVENT_INSTANCE/TextData)[1]', N'nvarchar(4000)');

        exec sp_send_dbmail @subject = 'Error in trigger', @body = @eventText;

    end
    else if @mt in (
        N'http://schemas.microsoft.com/SQL/ServiceBroker/Error', 
        N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    begin
        end conversation @dh;
    end

    commit

end
go

alter queue q with activation (
    status = on,
    max_queue_readers = 1,
    execute as = 'owner',
    procedure_name = 'usp_sq');
go

create table MyTable (a int);
go

create trigger MyTrigger 
    on MyTable 
    after insert
as
begin try
    if rand() > 0.5
        raiserror (N'Something horrible happend in this trigger!', 16,1);
end try
begin catch
    declare @error_message nvarchar(256) = cast(error_message() as nvarchar(256));
    exec sp_trace_generateevent 82, @error_message;
end catch
go

insert into MyTable (a) values (1);
insert into MyTable (a) values (2);
insert into MyTable (a) values (3);
insert into MyTable (a) values (4);
insert into MyTable (a) values (5);
insert into MyTable (a) values (6);
go