use account create table users (
userId varchar(10) not null, /*用户账号，主键*/ userName varchar(10) not null, /*用户名*/
userPassword varchar(10) not null, /*密码*/
idNo varchar(20) not null, /*身份证号码,唯一*/ userMoney
numeric(10,2) default 0, /*总金额，不能小于零*/ userStatus varchar(4) check (userStatus='使用' or userStatus='冻结') default '使用',
/*用户状态(使用，冻结)，默认是使用中*/
userAddress varchar(30) not null, /*用户地址*/ startTime DateTime, /*开户时间，应该是开户时的系统时间，不应该人为输入*/
primary key(userId), /*设置主键*/ )
--用户的状态一旦变成冻结状态，那么卡也应该不能使用 create trigger userstatus_update on users
after update as
if update(userStatus) begin
if (select userStatus from inserted) = '冻结'
update card set cardStatus = '冻结' where userId = (select userId from deleted)
if (select userStatus from inserted) = '使用'
update card set cardStatus = '使用' where userId = (select userId from deleted)
end
create table card (
cardId varchar(20) primary key, /*卡号，主键*/ userId varchar(10) not null, /*用户账号，外键，参照users表的userId*/
cardPassword varchar(10) not null, /*卡密码*/
cardStatus varchar(5) check (cardStatus in ('使用','冻结','挂失')) default '使用',
/*卡状态(使用，冻结，挂失),默认是使用*/ --cardMoney numeric(10,2) check(cardMoney >=0), foreign key (userId) references users(userId)
on delete cascade /*当删除用户帐号时，他的卡号会及联删除*/ ) --管理员表
create table admin (
adminId varchar(10) not null primary key, /*操作员号码，主键*/ adminName varchar(10) not null, /*操作员名字*/
adminPassword varchar(20) not null, /*操作员密码*/ BankAddress varchar(20) not null /*操作员所在银行地址*/ ) insert into admin values('001','沈万三','123','工商银行长安分行') --创建一个ATM表，至少应该含有编号以及ATM机里的总金额 create table ATM
(ATMId varchar(10) primary key, /*ATM机ID*/
ATMMoney numeric(20,2) check (ATMMoney >=0), /*ATM机里的总钱数,不允许用户的取款数大于它的总钱数，所以它的总钱数不能小于*/
ATMAddress varchar(20) /*ATM机的地点*/ )
insert into ATM values('000', 10000, '长安区') insert into ATM values('111', 50000, '长安区') --用户ATM机存取款信息表 create table ATMOutIn (
ATMId varchar(10), /*取款机号，外键，参照ATM表的ATMId*/ cardId varchar(20) not null, /*用户卡号，外键，参照card表的cardId*/ ATMTime DateTime, /*在ATM机存取款时间(应该是进行存取款时的系统时间*/ ATMCunQu numeric(10,2), /*ATM机存款金额*/
ATMSummary varchar(30), /*存取款摘要*/
balanceMoney numeric(10,2) default 0, /*经过操作后账号中的金额*/ foreign key(ATMId) references ATM(ATMId) on delete cascade, foreign key(cardId) references card(cardId) on delete cascade ) --创建一个触发器，当产生这一信息时，账号上的钱也会随之改动 create trigger ATMOutIn_insert on ATMOutIn after insert as
declare @num_rows int
select @num_rows = @@rowcount if @num_rows = 0 /*未插入成功*/ return
if @num_rows>0 /*数据插入成功*/ begin
update users set userMoney = userMoney + (select ATMCunQu from Inserted) where userId in (select userId from card c,inserted i where c.cardId = i.cardId)
update ATM set ATMMoney = ATMMoney + (select ATMCunQu from Inserted) where ATMId in (select ATMId from inserted) /*对应的atm机的总金额也会随之改变*/
declare @balanceMoney numeric(10,2) /*账号余额*/
select @balanceMoney = (select userMoney from users where userId in (select userId from card c,inserted i where c.cardId = i.cardId))
update ATMOutIn set balanceMoney = (@balanceMoney) where cardId in (select cardId from inserted) and atmtime in (select atmtime from inserted) /*完成了账号金额的修改，再将新的金额插入到信息表中，就是剩余的总余额了(注意修改的只是一个账号此次的操作*/
end
--用户银行存取款信息信息表 create table BankOutIn (
adminId varchar(10) not null, /*操作员号码，外键，参照操作员表的adminId*/ cardId varchar(20) not null, /*用户卡号,外键，参照用户信息表*/ BankTime DateTime, /*银行存款时间*/
BankCunQu numeric(10,2), /*银行存款*/ BankSummary varchar(30),
balanceMoney numeric(10,2) default 0, /*经过操作后账号中的金额*/ foreign key (adminId) references admin(adminId) on delete cascade, foreign key (cardId) references card(cardId) on delete cascade )
--创建一个触发器，当插入这条数据时会自动修改users表里的金额信息 create trigger BankOutIn_insert on BankOutIn for insert as
declare @num_rows int select @num_rows = @@rowcount if @num_rows = 0 /*未插入成功*/ return
if @num_rows>0 /*数据插入成功*/ begin
update users set userMoney = userMoney + (select BankCunQu from Inserted) where users.userId = (select userId from card c,inserted i where c.cardId = i.cardId)
declare @balanceMoney numeric(10,2)
数据库课程设计实验报告-银行账户管理系统
