SQL SERVER 2005 Error Handling in a transaction : Transaction Roll back « Transaction « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Transaction
3. Transaction Roll back

SQL SERVER 2005 Error Handling in a transaction

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>
           
       

Related examples in the same category