/
XACT_ABORT.sql
93 lines (60 loc) · 2.17 KB
/
XACT_ABORT.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
/*
For this Demo basically we use two tables: Countries and a Cities.
You can use the creation scripts uploaded at:
https://github.com/datoptim/BuildData/blob/master/CountryAndCity.sql
We'll see how XACT_ABORT works with two related tables.
You can visit www.datoptim.com
*/
/* First we list cities by a particular country */
EXEC usp_ListCities 'Bolivia'
GO
/* After we know which cities there are, we insert a new value.
Insert value having in mind we have to reference Country table for integrity.
It works fine in this case. You can verify with usp_ListCities
*/
INSERT INTO City VALUES ('Datoptim', 'Bolivia')
GO
/* Let's add some fake city names on this table.
What happens when you try to run all this next block of code?
Take a look that the fourth INSERT doesn't respect integrity.
*/
BEGIN TRAN
INSERT INTO City VALUES ('Z1', 'Bolivia')
INSERT INTO City VALUES ('Z2', 'Bolivia')
INSERT INTO City VALUES ('Z3', 'Bolivia')
INSERT INTO City VALUES ('Z4', 'Boliviazz')
INSERT INTO City VALUES ('Z5', 'Bolivia')
INSERT INTO City VALUES ('Z6', 'Bolivia')
COMMIT
/* Check again with usp_ListCities.
Did all the rows get inserted in the City table?
*/
EXEC usp_ListCities 'Bolivia'
GO
/* Now think about it. Is this the behavior we want?
Executing a block of code and having errors in some place,
would it be good to commit only the sentences with no errors?
Wouldn't be better to rollback all the block to avoid integrity corruptions?
That's what you have to tell in order to perform as you need.
*/
/* If you executed previous scrtips, run this one to clean original table
DELETE FROM City
WHERE City in ('Datoptim', 'Z1', 'Z2', 'Z3', 'Z5', 'Z6')
GO
Now try the same exercise this time setting XACT_ABORT.
Run the following as a block
*/
SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO City VALUES ('Z1', 'Bolivia')
INSERT INTO City VALUES ('Z2', 'Bolivia')
INSERT INTO City VALUES ('Z3', 'Bolivia')
INSERT INTO City VALUES ('Z4', 'Boliviazz')
INSERT INTO City VALUES ('Z5', 'Bolivia')
INSERT INTO City VALUES ('Z6', 'Bolivia')
COMMIT
/* You can see the same error message as before.
Let's check the content of the table
*/
EXEC usp_ListCities 'Bolivia'
GO