SQL Server のトランザクションは、try キャッチに記述しなければ、中間ステートメントがエラーを報告してもコミットされます。

CREATE TABLE [dbo]. [Person](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nvarchar](50) NULL,
    [Name] [nvarchar](50) NULL,
    [CreateTime] [datetime] NULL,
    [UpdateTime] [datetime] NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo]. [Person] ADD CONSTRAINT [DF_Person_CreateTime] DEFAULT (getdate()) FOR [CreateTime]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Person] ON [dbo]. [Person].
(
    [Code] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo]. [Book](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [BookCode] [nvarchar](50) NULL,
    [BookName] [nvarchar](50) NULL,
    [PersonCode] [nvarchar](50) NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo]. [Book] WITH CHECK ADD CONSTRAINT [FK_Book_Person] FOREIGN KEY([PersonCode])
REFERENCES [dbo]. [Person] ([Code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo]. [Book] CHECK CONSTRAINT [FK_Book_Person]
GO

BEGIN TRAN

INSERT INTO Person([Code],[Name])
VALUES('P003','Jack')


INSERT INTO [dbo]. [Book]([BookCode],[BookName],[PersonCode])
VALUES
('B001','B001','P003'),
('B002','B002','P003'),
('B003','B003','P003'),
('B004','B004','P003'),
('B005','B005','XXX') - Since the [PersonCode] column value 'XXX' of the Book table does not exist in the [Code] column of the Person table, the entire INSERT INTO [dbo]. [Book] statement will report an error and not execute

COMMIT

(1 row affected)
Message 547, level 16, status 0, line 7
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Book_Person". The conflict occurred in database "TestDB", table "dbo.Person", column 'Code'.
The statement has been terminated.

BEGIN TRAN

BEGIN TRY

    INSERT INTO Person([Code],[Name])
    VALUES('P003','Jack')


    INSERT INTO [dbo]. [Book]([BookCode],[BookName],[PersonCode])
    VALUES
    ('B001','B001','P003'),
    ('B002','B002','P003'),
    ('B003','B003','P003'),
    ('B004','B004','P003'),
    ('B005','B005','XXX') - Since the [PersonCode] column value 'XXX' of the Book table does not exist in the [Code] column of the Person table, the entire INSERT INTO [dbo]. [Book] statement will report an error and not execute


    COMMIT

END TRY
BEGIN CATCH

    ROLLBACK 

END CATCH