Mega Code Archive
SQL SERVER 2005 Error Handling in a transaction
<![CDATA[
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>
]]>