CODEWITHSUNDEEP
sqlsql-servert-sqltransactions
nicomp
nicomp

Reputation: 4647

Why doesn't this transaction rollback?

When I execute this script, the first INSERT works even though the second INSERT fails due to a NOT NULL constraint on Fluffiness. Why does the first row persist and why isn't it rolled back?

BEGIN TRAN
    INSERT INTO tCat(Cat, Fluffiness) VALUES('Sir Pounce A Lot', 8.0)
    INSERT INTO tCat(Cat)             VALUES('Violet')
COMMIT

Here is the table script

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tCat]
(
    [CatID] [INT] IDENTITY(1,1) NOT NULL,
    [Cat] [NCHAR](100) NOT NULL,
    [CatBreedID] [INT] NULL,
    [Fluffiness] [FLOAT] NOT NULL,

    CONSTRAINT [PK_tCat] 
        PRIMARY KEY CLUSTERED  ([CatID] ASC)
) ON [PRIMARY]

Upvotes: 2

Views: 61

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

You need to use SET XACT_ABORT ON:

SET XACT_ABORT ON;
BEGIN TRAN
    INSERT INTO tCat(Cat, Fluffiness) VALUES('Sir Pounce A Lot', 8.0)
    INSERT INTO tCat(Cat)             VALUES('Violet')
COMMIT

db<>fiddle demo

Related article: Error Handling in SQL by Erland Sommarskog

This table lists some common errors, and whether they abort the current statement or the entire batch.

+---------------------+-----------+
|        Error        |  Aborts   |
+---------------------+-----------+
| NOT NULL violation. | Statement |
+---------------------+-----------+

What I have said this far applies to when XACT_ABORT is OFF, which is the default. When you issue SET XACT_ABORT ON, the very most of the statement-terminating errors instead become batch-aborting errors

Upvotes: 6

Related Questions