Mega Code Archive
Update statement and check-constraints
<![CDATA[
4> CREATE TABLE MySavings(
5> AccountNum Int NOT NULL,
6> Amount Money NOT NULL
7> );
8> GO
1> CREATE TABLE MyChecking(
2> AcountNum Int NOT NULL,
3> Amount Money NOT NULL
4> );
5> GO
1> ALTER TABLE MyChecking ADD CONSTRAINT ckMinBalance
2> CHECK (Amount > $100.00)
3> GO
1> INSERT MySavings VALUES (12345, $1000.00)
2>
3> GO
(1 rows affected)
1>
2>
3>
4> INSERT MyChecking VALUES (12345, $1000.00)
5>
6> GO
(1 rows affected)
1>
2>
3>
4> BEGIN TRANSACTION
5> UPDATE MyChecking SET Amount = Amount - $990.00
6> WHERE AcountNum = 12345
7> UPDATE MySavings SET Amount = Amount + $990.00
8> WHERE AccountNum = 12345
9> COMMIT TRANSACTION
10> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 5
The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "master", table "dbo.MyChecking", column 'Amount'.
The statement has been terminated.
(1 rows affected)
1>
2>
3>
4>
5> BEGIN TRANSACTION
6> UPDATE MyChecking SET Amount = Amount - $990.00
7> IF @@ERROR != 0
8> BEGIN
9> ROLLBACK TRANSACTION
10> RETURN
11> END
12> ELSE
13> UPDATE MySavings SET Amount = Amount + $990.00
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 J\SQLEXPRESS, Line 6
The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "master", table "dbo.MyChecking", column 'Amount'.
The statement has been terminated.
1>
2> drop table MySavings;
3> drop table MyChecking;
4> GO
1>
]]>