Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Nested Transactions - Best Practice Expand / Collapse
Author
Message
Posted Tuesday, November 11, 2008 3:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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 . . . ;) shudder!

Post #600974
Posted Tuesday, November 11, 2008 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:44 PM
Points: 7,084, Visits: 14,684
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?
Post #600978
Posted Tuesday, November 11, 2008 3:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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. :)

I may look at changing the AFTER triggers to INSTEAD OF later down the track before ALPHA (unit) testing occurs.

Post #600980
Posted Tuesday, November 11, 2008 5:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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!!

Post #601033
Posted Tuesday, November 11, 2008 6:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #601036
Posted Tuesday, November 11, 2008 8:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601086
Posted Wednesday, November 12, 2008 2:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

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.
Post #601718
Posted Wednesday, November 12, 2008 3:43 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #601749
Posted Wednesday, November 12, 2008 3:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:35 PM
Points: 14, Visits: 71

:) 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


Post #601756
Posted Wednesday, November 12, 2008 5:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601790
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse