sql - 为什么这个事务不回滚？

Question

当我执行此脚本时，第一个 INSERT 工作，即使第二个 INSERT 由于对 Fluffiness 的 NOT NULL 约束而失败。为什么第一行仍然存在，为什么不回滚？

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

这是表格脚本

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]

Answer 1

您需要使用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

相关文章: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