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

Home
SQL Server / T-SQL
1.Aggregate Functions
2.Analytical Functions
3.Constraints
4.Cursor
5.Data Set
6.Data Type
7.Database
8.Date Timezone
9.Index
10.Insert Delete Update
11.Math Functions
12.Select Query
13.Sequence
14.Store Procedure Function
15.String Functions
16.Subquery
17.System
18.Table
19.Table Joins
20.Transact SQL
21.Transaction
22.Trigger
23.View
24.XML
SQL Server / T-SQL » Transaction » Transaction Roll back 
SQL SERVER 2005 Error Handling in a transaction


12>
13CREATE TABLE MySavings(AccountNum Int NOT NULL,
14>                        Amount Money NOT NULL)
15>
16CREATE 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>
22INSERT MySavings VALUES (12345, $1000.00)
23>
24INSERT MyChecking VALUES (12345, $1000.00)
25> GO
1>
2/*SQL SERVER 2005 Error Handling*/
3BEGIN 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>
2select from mysavings
3> go
AccountNum  Amount
----------- ---------------------
      12345             1990.0000
1>
2select 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
1.Rollback a transaction
2.Roll back a delete command
3.Rollback transaction on error
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.