Why the transaction insert 2 values when fails the first one?

Question

I have this Table:

TableAB
{
    IDTableA;
    IDTableB;
}

I have one row in the table, (1,2). Now I want to insert some others values, and I want to do it in a transaction. I use this:

begin
insert into TableAB (IDTAbleA, IDTAbleB) VALUES(1,2);
insert into TableAB (IDTAbleA, IDTAbleB) VALUES(1,3);
insert into TableAB (IDTAbleA, IDTAbleB) VALUES(1,3);
commit

I get an error of integrity in the first insert, but the second and third insert, insert the values in the table.

How I am using a transaction and fails the first one, why does it insert the other two rows? I thought inside a transaction, if somethings fails, all is aborted. Then in this case, which is the difference to use the transaction and don't use it?

Thanks.

"I thought inside a transaction, if somethings fails, all is aborted." -- true for some (most?) DBMS. For SQL Server you may need to change the settings, see https://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error — sticky bit, May 25 '19 at 18:37

score 2 · Accepted Answer · answered May 25 '19 at 18:21

I assume you should be wrapping things in a TRY...CATCH.

BEGIN TRY

    BEGIN TRANSACTION InsertValues;
    INSERT INTO TableAB (IDTAbleA, IDTAbleB) VALUES(1,2);
    INSERT INTO TableAB (IDTAbleA, IDTAbleB) VALUES(1,3);
    INSERT INTO TableAB (IDTAbleA, IDTAbleB) VALUES(1,3);
    COMMIT InsertValues;

END TRY
BEGIN CATCH

    ROLLBACK InsertValues;
    THROW;

END CATCH

score 1 · Answer 2 · answered May 25 '19 at 20:36

You need to use a TRY and CATCH and in the CATCH you have to ROLLBACK the transaction like already mentioned. The other way to do this is to SET XACT_ABORT ON as follows.

SET XACT_ABORT ON 

BEGIN TRAN
insert into TableAB (IDTAbleA, IDTAbleB) VALUES(1,2);
insert into TableAB (IDTAbleA, IDTAbleB) VALUES(1,3);
insert into TableAB (IDTAbleA, IDTAbleB) VALUES(1,3);
COMMIT TRAN

score 1 · Answer 3 · answered May 26 '19 at 07:00

Try this:

SET XACT_ABORT ON
BEGIN TRANSACTION;

BEGIN TRY

    INSERT INTO TableAB(IDTAbleA,IDTAbleB)VALUES(1, 2);
    INSERT INTO TableAB(IDTAbleA,IDTAbleB)VALUES(1, 3);
    INSERT INTO TableAB(IDTAbleA,IDTAbleB)VALUES(1, 3);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    BEGIN

        IF (XACT_STATE()) = -1
        BEGIN
            ROLLBACK TRANSACTION;
            THROW;
        END
        ELSE IF (XACT_STATE()) = 1
        BEGIN
            COMMIT TRANSACTION;
        END;

    END;

END CATCH;

Why the transaction insert 2 values when fails the first one?

3 Answers3