November 11, 2008 at 8:41 pm
Adam Hardy (11/11/2008)
Hopefully now we can dispense with the semantics as they are not really relevant to the question I asked.Adam.
Fine... answer my question... what specifically are you doing in the trigger?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2008 at 2:28 pm
In this particular example, the trigger is for insert on an InvoiceItem table (e.g. it stores lines on an invoice). On insert, stock has to be allocated against each invoice line by inserting into another table. If all invoice lines cab be allocated then the invoice is successfull otherwise the whole transaction (inserting invoice and invoiceitems and allocating stock) needs to be rolled back.
Hope that makes sense.
My whole beef is that, I believe all data modelling can and should be achieved with primary keys, foreign keys and when they fail, triggers. These 3 tools and transaction handling should form the basis to the base data integrity of the database. I'm beginning to think that MS Sql Servers quirks with transaction and error handling makes this task so cumbersome it's almost impossible.
Where I'm at now is I've got everything working except for the pesky error that gets thrown when a stored procedure exits with a different trancount to when it entered. Is there some way of turning this error off?
Adam.
November 12, 2008 at 3:43 pm
Adam Hardy (11/12/2008)
Where I'm at now is I've got everything working except for the pesky error that gets thrown when a stored procedure exits with a different trancount to when it entered. Is there some way of turning this error off?
Heh, I just spent two days on this one ...
The bottom line(s) are as follows:
1) It is generally a mistake when this happens, which is why an Error gets thrown. If I were writing standards, I would make sure that this was not permitted under that standard unless a specific exception was granted.
2) There is no way to turn this error off.
3) The only practical way for the code to manage this in the general case is to have the caller use TRY..CATCH.. to catch the error and then deal with it (that is: make @@TRANCOUNT right, or it will just happen again when the current proc exits).
4) There is a special case that is usually handle differently though: sometimes a trigger or subordinate procedure is being used to detect a condition, that it is then supposed to reject, but not throw an error. If it just rolls-back the current transaction, it will generate the 266 error when it exits. So instead, what I have seen is that, it does roll-back the current transaction, and then begins a new transaction to take its place, thus fooling the @@TRANCOUNT checker (and typically the caller as well).
Frankly, I think that subordinate rollbacks that cause @@TRANCOUNT to be different on exit probably should be throwing errors about 95% of the time anyway, either intentionally or because the code is just doing the wrong thing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 12, 2008 at 3:56 pm
🙂 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
November 12, 2008 at 5:47 pm
Adam, thanks for not only the explanation of what your trigger does and why DRI isn't going to do it for you, but also the trick you came up with for solving your problem. It's gonna useful for a lot of folks, especially me. Thanks again.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2008 at 5:55 pm
No worries Jeff,
Sorry for my narkyness earlier, I was somewhat frustrated by the situation. :ermm:
Hopefully this will help lots of others, this solution will become "best practice" within our organisation, maybe after some reviewing by other like minded people it could become a general "best practice".
Adam.
November 23, 2008 at 3:14 pm
I have since implemented the above methodology into our procedure and trigger writing, but have yet to do any testing, I am however confident that it will fulfil our needs.
If anyone would like to comment on what I've come up with, then please do, I'd appreciate the feedback.
Kind regards
Adam.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply