SQL Server/T-SQL/Transaction/Transaction Roll back
Содержание
Roll back a delete command
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> BEGIN TRAN 3> SELECT * FROM Employee 4> DELETE FROM Employee WHERE ID > 3 5> SELECT * FROM Employee 6> ROLLBACK TRAN 7> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(6 rows affected) ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
(3 rows affected) 1> 2> SELECT * FROM Employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
Rollback a transaction
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> -- Roll back delete all command 3> 4> BEGIN TRAN 5> SELECT COUNT(*) FROM Employee 6> DELETE Employee 7> SELECT COUNT(*) FROM Employee 8> ROLLBACK TRAN 9> GO
9
(9 rows affected)
0
(1 rows affected) 1> 2> SELECT COUNT(*) FROM Employee 3> GO
9
(1 rows affected) 1> 2> 3> drop table employee 4> GO 1>
</source>
Rollback transaction on error
<source lang="sql">
23> 24> CREATE TABLE MySavings(AccountNum Int NOT NULL, 25> Amount Money NOT NULL) 26> 27> CREATE TABLE MyChecking(AccountNum Int NOT NULL, 28> Amount Money NOT NULL) 29> 30> ALTER TABLE MyChecking ADD CONSTRAINT ckMinBalance 31> CHECK (Amount > $100.00) 32> 33> INSERT MySavings VALUES (12345, $1000.00) 34> 35> INSERT MyChecking VALUES (12345, $1000.00) 36> GO (1 rows affected) (1 rows affected) 1> 2> /*SQL SERVER 2000 Error Handling*/ 3> BEGIN TRANSACTION 4> UPDATE MyChecking SET Amount = Amount - $990.00 5> WHERE AccountNum = 12345 6> IF @@ERROR != 0 7> BEGIN 8> ROLLBACK TRANSACTION 9> RETURN 10> END 11> ELSE 12> UPDATE MySavings SET Amount = Amount + $990.00 13> WHERE AccountNum = 12345 14> IF @@ERROR != 0 15> BEGIN 16> ROLLBACK TRANSACTION 17> RETURN 18> END 19> ELSE 20> COMMIT TRANSACTION 21> GO Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 4 The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "master", table "dbo.MyChecking" The statement has been terminated. 1> 2> drop table MySavings; 3> drop table MyChecking; 4> GO 1> 2>
</source>
SQL SERVER 2005 Error Handling in a transaction
<source lang="sql">
12> 13> CREATE TABLE MySavings(AccountNum Int NOT NULL, 14> Amount Money NOT NULL) 15> 16> CREATE TABLE MyChecking(AccountNum Int NOT NULL, 17> Amount Money NOT NULL) 18> 19> ALTER TABLE MyChecking ADD CONSTRAINT ckMinBalance 20> CHECK (Amount > $100.00) 21> 22> INSERT MySavings VALUES (12345, $1000.00) 23> 24> INSERT MyChecking VALUES (12345, $1000.00) 25> GO 1> 2> /*SQL SERVER 2005 Error Handling*/ 3> BEGIN TRANSACTION 4> BEGIN TRY 5> UPDATE MyChecking SET Amount = Amount - $90.00 6> WHERE AccountNum = 12345 7> UPDATE MySavings SET Amount = Amount + $990.00 8> WHERE AccountNum = 12345 9> COMMIT TRANSACTION 10> END TRY 11> 12> BEGIN CATCH 13> RAISERROR 50001 "Transaction" 14> ROLLBACK TRANSACTION 15> END CATCH 16> GO 1> 2> select * from mysavings 3> go AccountNum Amount
---------------------
12345 1990.0000
1> 2> select * from mychecking 3> GO AccountNum Amount
---------------------
12345 910.0000
1> 2> drop table MySavings; 3> drop table MyChecking; 4> GO 1> 2>
</source>
