🙂 I'm glad I'm not the only one going through this pain!
Yeah, I agree with you mate, I only wanted to suppress it because I couldn't come up with another solution to my whole nested transactions and triggers situation.
BUT! FINALLY I've got a solution I'm happy with. I did some more reading and found that XACT_STATE() only displays user transactions, which means XACT_STATE() = 0 and @@TRANCOUNT = 1 when a trigger fires within an implicit transaction, alternatively XACT_STATE() = 1 and @@TRANCOUNT = 1 when a trigger fires within an explicit transaction. This means I can detect in the trigger if I should rollback when an error occurs, OR merely throw an error (to be caught by the calling functions try/catch block).
Here's the templates I've prepared for my dev team. Hopefully it will help others in the same boat.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[EXCEPTION]') AND type in (N'P', N'PC'))
DROP PROCEDURE [EXCEPTION]
GO
CREATE PROC [EXCEPTION]
@ERROR_Msg varchar(4000) = NULL OUTPUT,
@RAISE bit = 0,
@EVT_SOURCE varchar(20) = 'DATABASE',
@EVT_TYPE varchar(50) = 'EXCEPTION'
AS
BEGIN
SET NOCOUNT ON
DECLARE @cmd varchar(50),
@XML_OUTPUT xml,
@ERR_MESSAGE varchar(4000),
@EvtId int;
DECLARE@NUMBER [int],
@SEVERITY INT,
@STATE INT,
@LINE INT,
@PROCEDURE [varchar] (128),
@MESSAGE [varchar] (4000),
@SECTION [varchar] (255);
DECLARE @ERROR_INFO TABLE (
[EVENT] [varchar] (255) NULL ,
[PARAMETER] [int] NULL ,
[EVENT_INFO] [varchar] (1000) NULL
)
SELECT @NUMBER = ERROR_NUMBER(),
@SEVERITY = ERROR_SEVERITY(),
@STATE = ERROR_STATE(),
@LINE = ERROR_LINE(),
@PROCEDURE = ERROR_PROCEDURE(),
@MESSAGE = ERROR_MESSAGE()
BEGIN TRY
-- Return if there is no error information to log
IF (ISNULL(ERROR_NUMBER(),0) = 0) BEGIN
PRINT 'NO ERROR DETAILS'
RETURN;
END
-- Return if inside an uncommittable transaction.
-- Data insertion/modification is not allowed when a transaction is in an uncommittable state.
-- Therefore we will just passback the error information.
IF XACT_STATE() = -1
BEGIN
SET @RAISE = 1
END;
IF CHARINDEX(' 0 BEGIN
SET @XML_OUTPUT = CAST(@MESSAGE as xml)
END ELSE BEGIN
SET @cmd = 'DBCC INPUTBUFFER( ' + CAST(@@spid as varchar) + ') WITH NO_INFOMSGS ';
-- If there is a Transaction in progress (stuffed or otherwise) we cannot log the error.
-- So we will return the error info instead.
IF XACT_STATE() <> -1 OR @@TRANCOUNT = 0 BEGIN
INSERT INTO @ERROR_INFO ([EVENT], [PARAMETER], [EVENT_INFO])
EXEC (@cmd);
END ELSE BEGIN
INSERT INTO @ERROR_INFO ([EVENT], [PARAMETER], [EVENT_INFO])
VALUES ('','','')
END
SET @XML_OUTPUT = (
SELECT [NUMBER], [SEVERITY], [STATE], [LINE], [PROCEDURE], [MESSAGE], [EVENT], [PARAMETER], [EVENT_INFO]
FROM (
SELECT @NUMBER as [NUMBER], @SEVERITY as [SEVERITY], @STATE as [STATE], @LINE as [LINE], @PROCEDURE as [PROCEDURE],
@MESSAGE as [MESSAGE], [EVENT], [PARAMETER], [EVENT_INFO]
FROM @ERROR_INFO ) AS EXCEPTION
FOR XML AUTO, ELEMENTS
)
END
SET NOCOUNT OFF
IF @RAISE = 1 BEGIN
SET @ERROR_Msg = CAST(@XML_OUTPUT as varchar(4000))
END ELSE BEGIN
-- PLACE YOUR OWN ERROR LOGGING HERE
PRINT ISNULL(CAST(@XML_OUTPUT as varchar(max)), 'BLANK')
END
END TRY
BEGIN CATCH
SET @ERR_MESSAGE = 'ERROR IN HANDLER :' + isnull(ERROR_PROCEDURE(),'UNKNOWN PROCEDURE ') + ' - ' + isnull(ERROR_MESSAGE(),'NO MESSAGE');
RAISERROR(@ERR_MESSAGE, 16,1);
RETURN -1;
END CATCH;
RETURN 0;
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[IsInImplicitTransaction]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [IsInImplicitTransaction]
GO
CREATE Function [IsInImplicitTransaction] ()
RETURNS bit
AS
BEGIN
DECLARE @RetVal bit
IF (XACT_STATE() = 0 and @@TRANCOUNT = 1)
SET @RetVal = 1
ELSE
SET @RetVal = 0
RETURN @RetVal
END;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spFOO') AND type in (N'P', N'PC'))
DROP PROCEDURE spFOO
GO
CREATE PROC spFOO ()
AS
SET NOCOUNT ON
DECLARE @Trancount int
SET @Trancount = @@TRANCOUNT
BEGIN TRY
IF @Trancount = 0 BEGIN TRANSACTION
-- INSERT T-SQL CODE HERE
IF @Trancount = 0 COMMIT TRANSACTION
END TRY BEGIN CATCH
IF @Trancount = 0 BEGIN
ROLLBACK TRANSACTION
EXEC [EXCEPTION]
END ELSE BEGIN
DECLARE @ErrorMsg varchar(4000)
EXEC [EXCEPTION] @ErrorMsg OUTPUT, 1
RAISERROR(@ErrorMsg,11,1)
END
END CATCH
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[tr_FOO]'))
DROP TRIGGER [tr_FOO]
GO
CREATE TRIGGER [tr_FOO] on [FOO] FOR INSERT
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
-- INSERT TRIGGER CODE HERE
END TRY BEGIN CATCH
IF dbo.[IsInImplicitTransaction]() = 1 BEGIN
ROLLBACK
END
DECLARE @ErrorMsg varchar(4000)
EXEC [EXCEPTION] @ErrorMsg OUTPUT, 1
RAISERROR(@ErrorMsg,11,1)
END CATCH
END
GO