1 USE stus
2 go
3 IF EXISTS(SELECT * FROM sysobjects WHERE name='cards')
4 DROP TABLE cards
5 go
6 CREATE TABLE cards--银行卡信息表
7 (
8 cardno CHAR(19) NOT NULL,--卡号19位
9 curType CHAR(5) NOT NULL,--货币内型
10 savingType CHAR(8) NOT NULL,--存储内型
11 opendate DATETIME NOT NULL,--开户时间
12 openmoney MONEY NOT NULL,--开户金额
13 balance MONEY NOT NULL,--余额
14 )
15 GO
16
17 ALTER TABLE cards
18 ADD CONSTRAINT pk_cardno PRIMARY KEY(cardno),
19 CONSTRAINT ck_cardno CHECK(cardno LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
20 CONSTRAINT df_curType DEFAULT('RMB')FOR curType,
21 CONSTRAINT CK_savingType CHECK(savingType IN ('活期','定活两便','定期')),
22 CONSTRAINT DF_openDate DEFAULT(getdate()) FOR openDate,
23 CONSTRAINT CK_openMoney CHECK(openMoney>=1),
24 CONSTRAINT CK_balance CHECK(balance>=1)
25 GO
26
27 INSERT INTO cards(cardno,savingType,openmoney,balance)
28 SELECT '1010 3576 1234 5678','活期',1000,1000 UNION
29 SELECT '1010 3576 1212 1134','定期',1,1
30 go
31 CREATE TABLE trans--交易信息表
32 (
33 transDate DATETIME NOT NULL,--交易时间
34 transType CHAR(4) NOT NULL,--只能是存入/支取
35 cardID CHAR(19) NOT NULL,--卡号,外健，可重复索引
36 transMoney MONEY NOT NULL,--交易金额
37 remark TEXT --备注 可选输入，其他说明
38 )
39 GO
40 ALTER TABLE trans
41 ADD CONSTRAINT CK_transType CHECK(transType IN ('存入','支取')),
42 CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cards(cardno),
43 CONSTRAINT CK_transMoney CHECK(transMoney>0),
44 CONSTRAINT DF_transDATE DEFAULT(getdate()) FOR transDate
45 GO
46 SELECT * FROM cards
47 SELECT * FROM trans
48
49 UPDATE cards SET balance=1 WHERE cardno='1010 3576 1212 1134'
50 TRUNCATE TABLE trans--删除表中的数据
51
52 USE stus
53 GO
54 BEGIN TRAN
55 DECLARE @error INT =0
56 DECLARE @money MONEY=500
57 UPDATE cards SET balance=balance+@money WHERE cardno='1010 3576 1212 1134'
58 IF @@ERROR>0 OR @@ROWCOUNT=0
59 SET @error=@error+1
60 UPDATE cards SET balance=balance-@money WHERE cardno='1010 3576 1234 5678'
61 IF @@ERROR>0 OR @@ROWCOUNT=0
62 SET @error=@error+1
63 INSERT INTO trans(transType,cardID,transMoney,remark)
64 SELECT '支取','1010 3576 1234 5678',@money,'网上银行转账' UNION
65 SELECT '存入','1010 3576 1212 1134',@money,'网上银行转账'
66 IF @error>0
67 ROLLBACK TRAN
68 ELSE
69 COMMIT TRAN
70 go
