Rollback transaction on error : Error « Transact SQL « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Transact SQL
3. Error

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