Stwórzmy bazę TEST, a następnie tabelę TABLE_TEST składają się z dwóch kolumn : ID i nazwy oraz z kluczem publicznym na kolumnie ID.
CREATE DATABASE [TEST]
Commands completed successfully.
USE [TEST]
GO
CREATE TABLE [dbo].[TABLE_TEST](
[id] [int] NOT NULL,
[nazwa] [nchar](10) NULL,
CONSTRAINT [PK_TableTest] 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
Commands completed successfully.
Następnie spróbujmy wykonać polecenie:
BEGIN TRANSACTION
INSERT INTO TABLE_TEST (ID,nazwa) VALUES(1,'rower');
INSERT INTO TABLE_TEST (ID,nazwa) VALUES(1,'wrotki');
COMMIT TRANSACTION;
(1 row affected) Msg 2627, Level 14, State 1, Line 22 Violation of PRIMARY KEY constraint 'PK_TableTest'. Cannot insert duplicate key in object 'dbo.TABLE_TEST'. The duplicate key value is (1). The statement has been terminated.
Jako że dane w kolumnie ID muszą być unikalne, zwróciło błąd jednak pierwsza operacja INSERT została zapisana do tabeli.
SELECT * FROM [TEST].[dbo].[TABLE_TEST]
id nazwa ----------- ---------- 1 rower (1 row affected)
Jest tak ponieważ domyślnie transakcje nie jest anulowane w przypadku wystąpienia błędów w trakcie jej wykonywania, tylko jest kontynuowana. Aby temu zapobiec należy ustawić zmienną XACT_ABORT na wartość ON:
select 16384 & @@OPTIONS as '16384_@OPTIONS'
16384_@OPTIONS
--------------
0
(1 row affected)
Ustawiamy zmienną XACT_ABORT na wartość ON.
SET XACT_ABORT ON;
Commands completed successfully.
Teraz wyczyścimy naszą tabelę z danych
TRUNCATE TABLE TEST.TABLE_TEST
Commands completed successfully.
Ponownie wykonujemy nasze polecenie INSERT
BEGIN TRANSACTION
insert into TABLE_TEST (ID,nazwa) values(1,'rower');
insert into TABLE_TEST (ID,nazwa) values(1,'wrotki');
COMMIT TRANSACTION;
(1 row affected) Msg 2627, Level 14, State 1, Line 21 Violation of PRIMARY KEY constraint 'PK_TableTest'. Cannot insert duplicate key in object 'dbo.TABLE_TEST'. The duplicate key value is (1).
Zwróciło nam błąd, ale dane nie zostały zapisane do tabeli
SELECT * FROM [TEST].[dbo].[TABLE_TEST]
id nazwa ----------- ---------- (0 rows affected)
XACT_ABORT powoduje, że w przypadku wystąpienia błędu cała transakcja jest anulowana (nastąpi rollback).
Oczywiście działa to tylko w przypadku stosowanie opcji transakcji BEGIN/COMMIT.
Jeśli wykonamy INSERT pojedynczo pierwszy zostanie zapisany do tabeli
insert into TABLE_TEST (ID,nazwa) values(1,'rower');
insert into TABLE_TEST (ID,nazwa) values(1,'wrotki');
(1 row affected) Msg 2627, Level 14, State 1, Line 21 Violation of PRIMARY KEY constraint 'PK_TableTest'. Cannot insert duplicate key in object 'dbo.TABLE_TEST'. The duplicate key value is (1).
SELECT * FROM [TEST].[dbo].[TABLE_TEST]
id nazwa ----------- ---------- 1 rower (1 row affected)
