October 7, 2010 at 3:45 am
Hello,
This post has to do with another I posted yesterday. It's a about triggers.
I wrote a trigger that fires when a table gets updated/inserted. It works fine, but maybe it will fail in the future for whatever reason.
Question: how can I get an alert or notification that the trigger has failed (and if possible the error number & description)?
I can't find any information anywhere on this subject.....
October 7, 2010 at 4:36 am
You could use db mail to send an alert to your mailbox.
For some reason I feel that this is not exactly what you want. Can you elaborate a bit more?
-- Gianluca Sartori
October 7, 2010 at 7:57 am
Hi,
Actually what I would like best is a Stored Procedure I wrote getting fired; it logs all error info into a logfile. Something like this:
TRIGGER ON UPDATE, INSERT
BEGIN TRY
... trigger-code
END TRY
BEGIN CATCH
execute MyLoggingSP
END CATCH
This doesn't work, because if there is any error in the "trigger-code", the logging does not occur.
However, at this point I would appreciate any way to get an alert when a trigger fails.... even email if there is no other way!
Thanks,
Ray
October 7, 2010 at 8:10 am
Probably your logging procedure was rolled back by an outer transaction.
Try using db mail.
-- Gianluca Sartori
October 7, 2010 at 9:07 am
If the trigger fails, you will not likely be able to send a message since it will roll back. Potentially you could send mail, but not necessarily.
I would recommend you use something like a stored procedure that does the DML and can respond to an error.
October 12, 2010 at 6:14 pm
Steve Jones - SSC Editor (10/7/2010)
If the trigger fails, you will not likely be able to send a message since it will roll back. Potentially you could send mail, but not necessarily.I would recommend you use something like a stored procedure that does the DML and can respond to an error.
My answer is based on SQL2005 so YMMV in 2008... You can send an email with enough information to follow up on processes that roll back due to a trigger failure. The sample code is just that-- a sample. The triggers I have to deal with do other stuff-- but the way to do the RAISERROR is the same.
I have a similar need. Our vendor has a RAISERROR in a trigger but we just discovered it didn't use the "WITH LOG" clause and apparently his app doesn't handle the error either. We discovered this because of bad data.
To test this approach, create a table:
CREATE TABLE [dbo].[__TRIGGERTEST]([rowid] [int] IDENTITY(1,1) NOT NULL,[foo] [char](10) NULL,[bar] [char](10) NULL)
Add some rows.
Then create a DELETE trigger on the table like this one. It will prevent DELETE from the table (nope it does not deal with TRUNCATE TABLE) and write to the SQL Error Log when deletes are attempted:
USE [Admin]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
CREATE TRIGGER [dbo].[trg_DEL_TRIGGERTEST]
ON [dbo].[__TRIGGERTEST]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@uid varchar(max),
@rowcount int,
@errmessage varchar(440),
@errproc varchar(400)
SELECT
@uid = '',
@rowcount = 0,
@errproc = DB_NAME() + '.' + SCHEMA_NAME() + '.' + OBJECT_NAME(@@PROCID,DB_ID())
-- NOTE: rowid is an integer identity column in the table
SELECT
@uid = @uid + CAST(rowid AS VARCHAR(10)) + ', '
FROM DELETED
ORDER BY rowid
--
SELECT @rowcount = @@rowcount
-- if rows were deleted undo it.
IF @rowcount > 0 BEGIN
SET @errmessage = char(13) + char(10) + 'Trigger Failed: [%s]; Naughty, naughty. You tried to delete [%u] row(s). UID(s) = [%s]'
SET @uid = LEFT(@uid,LEN(@uid) - 1)
RAISERROR (@errmessage,15,1,@errproc,@rowcount,@uid) WITH LOG
ROLLBACK TRANSACTION
END
-- The RAISERROR writes to the SQL Log
-- Then a SQL ALERT will email you to let you know about the failure
END
Now that you have this, create a simple SQL ALERT: for testing I set DBNAME = ADMIN and SEVERITY = 15 and MESSAGE TEXT to Trigger Failed. Goto the Response tab and check "Notify Operators" and make your choices. Goto Options and check "Email".
Finally run each of these commands separately:
delete from admin.dbo.__TRIGGERTEST where rowid = 2
delete from admin.dbo.__TRIGGERTEST where rowid < 50
delete from admin.dbo.__TRIGGERTEST
There's a length limit so you may see "UID(s) = [....]" if they all fit or "UID(s) = [...." if they don't.
Anyway, it works for me. A sample email is:
DATE/TIME:10/12/2010 4:33:20 PM
DESCRIPTION:Error: 50000 Severity: 15 State: 1
Trigger Failed: [Admin.dbo.trg_DEL_TRIGGERTEST]; Naughty, naughty. You tried to delete [520] row(s). UID(s) = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply