触发失败发送电子邮件

问题描述：

我已成功配置并能够通过调用msdb.dbo.sp_send_dbmail存储过程使用SQL Server 2008 R2上的“数据库邮件”发送电子邮件。我可以通过将它放入try-catch块来捕捉触发器中查询的失败。触发失败发送电子邮件

但我需要做的是，当触发器无法执行查询时发送电子邮件。但是我不能在catch块中执行上面的存储过程。它给了我下面的错误。

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.

是否有其他方式发送触发失败的电子邮件？或者如何解决这个问题？

答

触发失败将始终导致事务回滚，因此无法执行任何事务操作，如调用sp_send_dbmail。一个技巧是利用自定义事件探查器跟踪事件，因为它们是在独立事务中发送的：

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

触发失败发送电子邮件

相关推荐