SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nested Transactions - Best Practice


Nested Transactions - Best Practice

Author
Message
Adam Hardy-416657
Adam Hardy-416657
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 122
hmm, Ok Matt thanks.

BEFORE triggers! I wish! Bloody SQL Server doesn't support them.

My triggers used to be "INSTEAD OF", but yeah I wasn't too impressed with the whole maintenance issue of keeping the insert within the trigger up to date with all the new columns added. Now if I could just write a DDL trigger to keep the DML trigger up to date when the table structure changes . . . Wink shudder!
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12111 Visits: 18567
Sorry - I did in fact mean Instead of (since they can be made to implement like a BEFORE sorta like).

I don't let tables get updated ad-hoc, so I can't say I've thought of that as a big maintenance task. But I suppose it would be ugly if the table changed a lot....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Adam Hardy-416657
Adam Hardy-416657
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 122
Well we don't really either, it's just that this DB is not in production yet and we're still heavily in development (with DB changes coming in every day) so I have to remember to keep updating the trigger if the table changes. . . .

. . . and my memory aint that good. Smile

I may look at changing the AFTER triggers to INSTEAD OF later down the track before ALPHA (unit) testing occurs.
Adam Hardy-416657
Adam Hardy-416657
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 122
Hey I've been playing around this morning, with some example SP and example triggers, trying to write an error handler and testing how to structure the try\catch blocks etc.

So I've got one stored proc which opens a transaction then does an update (which fires a trigger). The weird thing is the @@TRANCOUNT when printed from within the Trigger is 1? Not 2? I don't understand, shouldn't it be 2, 1 for the opened transaction within the SP and 1 for the implicit trigger transaction?.

Also when doing the same update outside of the stored procedure (with no transaction), the same the printed out Trancount is still 1!!
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14588 Visits: 9518
Adam Hardy (11/11/2008)
I've got one stored proc which opens a transaction then does an update (which fires a trigger). The weird thing is the @@TRANCOUNT when printed from within the Trigger is 1? Not 2? I don't understand, shouldn't it be 2, 1 for the opened transaction within the SP and 1 for the implicit trigger transaction?.


No, Triggers will only start a transaction if @@TRANCOUNT is 0 when the trigger is activated.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84677 Visits: 41069
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Adam Hardy-416657
Adam Hardy-416657
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 122
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.
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14588 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Adam Hardy-416657
Adam Hardy-416657
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 122
Smile 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84677 Visits: 41069
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search