I'm using transactions for the first time, so I might be asking a silly question.
I want to insert data in 3 tables:
Table1(p1,p2,p3)
Table2(q1,q2)
Table3(t3,fk1,fk2)
For example, if something goes wrong and data cannot be inserted into Table2
, data from Table1
will not be lost and Table3
remains unchanged (and viceversa).
I've tried two versions so far, but none of them is satisfying.
Version 1:
CREATE PROCEDURE InsertInto(@p1,@p2,@p3,@q1,@q2,@t3))
AS BEGIN
BEGIN TRAN
SET XACT_ABORT OFF
SAVE TRANSACTION point1
BEGIN TRY
DECLARE @fk1 INT
INSERT INTO Table1 VALUES (@p1,@p2,@p3)
SELECT @fk1 = Table1.Id FROM Table1 WHERE Table1.p1 = @p1
SAVE TRANSACTION point2
BEGIN TRY
DECLARE @fk2 INT
INSERT INTO Table2 VALUES (@q1,@q2)
SELECT @fk2 = Table2.Id FROM Table2 WHERE Table2.q1 = @q1
SAVE TRANSACTION point3
BEGIN TRY
INSERT INTO Table3 VALUES (@t3, @fk1, @fk2)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION point3
COMMIT TRAN
END CATCH
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION point2
COMMIT TRAN
END CATCH
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION point1
COMMIT TRAN
END CATCH
END
But if data cannot be inserted in Table1
, then possible data for Table2
is lost and I don't want to lose anything. So, I've tried to split it.
Version 2:
CREATE PROCEDURE InsertInto(@p1,@p2,@p3,@q1,@q2,@t3)
AS
BEGIN
BEGIN TRAN
SET XACT_ABORT OFF
SAVE TRANSACTION point1
BEGIN TRY
DECLARE @fk1 INT
INSERT INTO Table1 VALUES (@p1,@p2,@p3)
SELECT @fk1 = Table1.Id FROM Table1 WHERE Table1.p1 = @p1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION point1
COMMIT TRAN
END CATCH
SAVE TRANSACTION point2
BEGIN TRY
DECLARE @fk2 INT
INSERT INTO Table2 VALUES (@q1,@q2)
SELECT @fk2 = Table2.Id FROM Table2 WHERE Table2.q1 = @q1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION point2
COMMIT TRAN
END CATCH
SAVE TRANSACTION point3
BEGIN TRY
INSERT INTO Table3 VALUES (@t3,@fk1,@fk2)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION point3
COMMIT TRAN
END CATCH
END
But if Insert into Table2 fails, I get this:
(1 row(s) affected)
(0 row(s) affected)
Msg 628, Level 16, State 0, Procedure InsertInto, Line 26 (second BEGIN CATCH)
Cannot issue SAVE TRANSACTION when there is no active transaction.
How can I do this right?
SAVE TRAN requires transaction count > 0, thus you must have committed your transaction in the previous CATCH block. You have several options:
1) Replace your SAVE TRAN statement with the following (you can use the same savepoint name, however a rollback will rollback only to the last savepoint):
IF @@TRANCOUNT = 0
BEGIN TRAN;
ELSE
SAVE TRAN tran1;
2) In the CATCH block, add a BEGIN TRAN after the COMMIT TRAN
BEGIN CATCH
ROLLBACK TRANSACTION point1
COMMIT TRAN
BEGIN TRAN
END CATCH
3) Remove all the COMMIT TRAN inside the CATCH blocks, and do a single COMMIT at the end.