‘Error Management’ Kategorisi için Arşiv
Exception Handling Best Practices in .NET
Yazan: esersahin 18/05/2009
Yazı kategorisi: Error Management, Exception | » yorum bırak;
Rollback transaction on error
Yazan: esersahin 20/03/2009
http://www.java2s.com/Code/SQLServer/Transaction/Rollbacktransactiononerror.htm
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>
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>
Yazı kategorisi: Error Management, Transact-SQL, Transaction | » yorum bırak;
