February 9, 2011 at 3:40 am
I want to write a trigger that send an e-mail if a certain condition occurs.
I dont want the current transaction to wait for the e-mail to be sent nor be affected by any error messages that may occur from the call to msdb.dbo.sp_send_dbmail.
Is this possible.
February 9, 2011 at 5:24 am
sp_send_dbmail is safe to use in a trigger;
sp_send_dbmail is asynchronous, so it returns without errors immediately...your trigger would not wait for it.
the only way i've encountered so far that it would interrupt your trigger if you selected a non-existent profile; it then returns an error; but if the profile exists, the send-success or send-fail is outside of the trigger in the async process, and you have to check the views in msdb to see if they sent or failed.
Lowell
February 9, 2011 at 6:15 am
The worry I have is that an error may occur and as its sending out a warning message I can't have the transaction rolled back under any circumstance.
I would therefore prefer a means of executing the code outside of the current transaction.
February 9, 2011 at 7:04 am
in that case I'd recommend not using a trigger at all.
with or without the trigger you could do two things that i can think of:;
1. skip the trigger and have a job scan for changes to that table, say once every x minutes, and send the email completely outside of the trigger/transaction.
2. use a trigger and hand it off to a broker., I'm not even sure if the broker is separate from the SQL system, but if the broker service is not working, it would fail inside the trigger and roll back.
I don't have an email/broker example...all my broekr examples ahve to do with auditing; might take a bit of googling for a decent example for that.
Lowell
February 9, 2011 at 7:15 am
Cool thanks.
February 10, 2011 at 7:34 am
What I've done in these cases is use a TRY/CATCH block leaving the CATCH block empty.
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail <your parameters here>
END TRY
BEGIN CATCH
END CATCH
I know that there are some cases where TRY won't trap the error, but I'm not sure what those cases are. I don't believe that this is one of them.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 10, 2011 at 8:10 am
drew.allen (2/10/2011)
What I've done in these cases is use a TRY/CATCH block leaving the CATCH block empty.
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail <your parameters here>
END TRY
BEGIN CATCH
END CATCH
I know that there are some cases where TRY won't trap the error, but I'm not sure what those cases are. I don't believe that this is one of them.
Drew
Have you successfully trapped errors in a trigger with Try/Catch? I've experimented with it before and I get an error message which makes me think it isn't going to try/catch correctly.
Such as:
--Create Sample Table
CREATE TABLE AATest(
A int)
GO
--Add a Trigger
CREATE TRIGGER AATest_Trig ON AATest
FOR INSERT
AS
BEGIN TRY
DECLARE @a int
SELECT @a = 1/0
END TRY
BEGIN CATCH
END CATCH
GO
-- Attempt an Insert
INSERT INTO AATest(A)
SELECT 1
GO
-- Verify row is not there
SELECT * FROM AATest
GO
--Cleanup
DROP TABLE AATest
Result:
Msg 3616, Level 16, State 1, Line 3
Transaction doomed in trigger. Batch has been aborted.
(0 row(s) affected)
February 11, 2011 at 7:40 am
TRY/CATCH is not a trustworthy construct. All kinds of limitations, gotchas, provisos, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply