Script design many-to-many insertion with partial rollback

Question

I am trying to understand partial roll backs, in TSQL. Following a tutorial i found this example but I do not understand / find can you save to a table the second object if the first fails?

BEGIN TRAN
PRINT 'First Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES ('Tom')

BEGIN TRAN
PRINT 'Second Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES ('Dick')

ROLLBACK TRAN
PRINT 'Rollback: ' + CONVERT(VARCHAR,@@TRANCOUNT)

So if 'Tom' fails can I save 'Dick' ? I need this, for achieving a many-to-many roll back transaction. So i create Obj A, Obj B and if both succes add Obj C, if not roll back failure, and keep the successing INSERT query.

Maybe I'm missing something, but if you want to keep the first two inserts how are you rolling back? — JAQFrost, May 29 '13 at 21:21
In case if something goes wrong... I need to be able to keep the succesful aprt of the query — Bogdan M., May 29 '13 at 22:21

score 1 · Accepted Answer · answered May 29 '13 at 22:43

It looks like you just want to commit the transactions as they succeed. The code below keeps Tom, even if your script chokes on Dick...

CREATE TABLE #People
    ( 
        VAL VARCHAR(100)
    );

DECLARE @stop BIT = 0;
BEGIN TRY
    BEGIN TRAN
    PRINT 'First Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

    INSERT INTO #People VALUES ('Tom')
    COMMIT 
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
    SET @stop = 1;
END CATCH;
SELECT * FROM #People;

IF @stop = 0
BEGIN TRY
    BEGIN TRAN
    PRINT 'Second Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

    INSERT INTO #People VALUES ('Dick');
    SELECT * FROM #People;

    RAISERROR(
        'Choke on Dick' -- stop snickering
        , 16
        , 1
        );

    COMMIT;
END TRY
BEGIN CATCH
    PRINT 'Rollback: ' + CONVERT(VARCHAR,@@TRANCOUNT)
    ROLLBACK TRAN
END CATCH

SELECT * FROM #People;

DROP TABLE #People;

Script design many-to-many insertion with partial rollback

1 Answers1