use master
go
if exists(select*from sysdatabases where name='bbsDB')
drop database bbsDB
create database bbsDB
on primary
(
name='bbsDB_data',
filename='D:\project\bbsDB_data.mdf',
size=10mb
)
log on
(
name='bbsDB_log',
filename='D:\project\bbsDB_log.ldf',
size=10mb
)
go
use bbsDB
go
create table bank
(
customerName char(8) not null,--顾客姓名
cardID char(10) not null,--卡号
currentMoney Money not null,--当前余额
)
go
create table transInfo
(
cardID char(10) not null,--卡号
transType char(4) not null,--交易类型
transMoney money not null,--交易金额
transDate datetime not null,--交易时间
)
go
alter table bank
add constraint ck_currentMoney check(currentMoney>=1)
alter table transInfo
add constraint df_transDate default(getDate())for transDate,constraint ck_transType check(transType in('存入','支取'))
go
insert into bank(customerName,cardID,currentMoney)values('张三','100010001',2000)
--insert into bank(customerName,cardID,currentMoney)values('李四','100010002',1)
print '------取款前的余额------'
select*from bank
go
begin transaction
declare @errorSum int
declare @myMoney Money
set @myMoney=1000 --取款金额
set @errorSum=0
--取款
insert into transInfo(cardID,transType,transMoney)values('100010001','支取', @myMoney)
set @errorSum=@errorSum+@@error
update bank set currentMoney=currentMoney-@myMoney where cardID='100010001'
set @errorSum=@errorSum+@@error
print'------取款事务过程中余额和交易信息------'
select*from bank
select*from transInfo
if @errorSum<>0
begin
print'交易失败，回滚事务'
rollback transaction
end
else
begin
print '交易成功，提交事务'
commit transaction
end
go
print'-------取款事务结束后的余额和交易信息------'
select*from bank
select*from transInfo
go
go
begin transaction
declare @errorSum int
declare @myMoney Money
set @myMoney=5000 --存入金额
set @errorSum=0
--存入
insert into transInfo(cardID,transType,transMoney)values('100010001','存入',@myMoney)
set @errorSum=@errorSum+@@error
update bank set currentMoney=currentMoney+@myMoney where cardID='100010001'
set @errorSum=@errorSum+@@error
print'------存款事务过程中余额和交易信息------'
select*from bank
select*from transInfo
if @errorSum<>0
begin
print'交易失败，回滚事务'
rollback transaction
end
else
begin
print '交易成功，提交事务'
commit transaction
end
go
print'-------存款事务结束后的余额和交易信息------'
select*from bank
select*from transInfo
go
