T—SQL事务 

 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

 

posted @ 2013-05-27 14:28  宁静思远  阅读(315)  评论(0)    收藏  举报