Insert all statements in a batch or to roll back the entire statement group if an error occurs
20>
21>
22> CREATE TABLE employee (emp_no INTEGER NOT NULL,
23> emp_fname CHAR(20) NOT NULL,
24> emp_lname CHAR(20) NOT NULL,
25> dept_no CHAR(4) NULL)
26>
27> insert into employee values(1, 'Matthew', 'Smith', 'd3')
28> insert into employee values(2, 'Ann', 'Jones', 'd3')
29> insert into employee values(3, 'John', 'Barrimore','d1')
30> insert into employee values(4, 'James', 'James', 'd2')
31> insert into employee values(5, 'Elsa', 'Bertoni', 'd2')
32> insert into employee values(6, 'Elke', 'Hansel', 'd2')
33> insert into employee values(7, 'Sybill', 'Moser', 'd1')
34>
35> select * from employee
36> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no emp_fname emp_lname dept_no
----------- -------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected)
1>
2> -- Use exception handling to insert all statements in a batch or to roll back the entire statement group if an error occurs.
3>
4> BEGIN TRY
5> BEGIN TRANSACTION
6> insert into employee values(11111, 'Ann', 'Smith','d2')
7> insert into employee values(22222, 'Matthew', 'Jones','d4')
8> insert into employee values(33333, 'John', 'Barrimore', 'd2000000000000')
9> COMMIT TRANSACTION
10> PRINT 'Transaction committed'
11> END TRY
12> BEGIN CATCH
13> ROLLBACK
14> PRINT 'Transaction rolled back'
15> END CATCH
16> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
Transaction rolled back
1> select * from employee
2> GO
emp_no emp_fname emp_lname dept_no
----------- -------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected)
1> drop table employee
2> GO
1>
Related examples in the same category