Rollback transaction on error : Transaction Roll back « Transaction « SQL Server / T-SQL






Rollback transaction on error


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 JAVA2S\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>
           
       








Related examples in the same category

1.Rollback a transaction
2.Roll back a delete command
3.SQL SERVER 2005 Error Handling in a transaction