数据库课程设计-银行储蓄系统完全代码.docxVIP

数据库课程设计完全代码 --建库 create database Bank on primary ( name = Bank, filename=D:\project\Bank.mdf, size = 5, maxsize = 100, filegrowth = 10% ) log on ( name = Bank_log, filename = D:\project\Bank_log.ldf, size=2, filegrowth=1 ) go --建表 use Bank create table Depositors( BNo varchar(20) primary key, --账号 BName varchar(20) not null, --姓名 BPassword char(6) not null check(len(BPassword) = 6), --密码 BID varchar(20) not null, --身份证号 BSex char(2) not null check(BSex = 男 or BSex = 女), --性别 BStyle varchar(20) not null check(BStyle = 活期存款 or BStyle = 定期存款), --业务类型 BDate datetime not null, --开户时间 BYear int not null check(BYear = 0 or BYear = 1 or BYear = 2 or BYear = 3), --存款期限,0表示活期 BMoney decimal(10,4) not null check(BMoney = 0) --账户余额 ) create table CurrentAccounts( nID int primary key identity(1,1), --流水号 BNo varchar(20) not null references Depositors(BNo), --账号 BName varchar(20) not null, --姓名 BStyle varchar(20) not null check(BStyle = 活期存款 or BStyle = 活期取款), --操作类型 BCash decimal(10,4) null check(BCash = 0), --操作金额 BDate datetime not null, --操作时间 BInterest decimal(10,4) null check(BInterest = 0), --利息 BMoney decimal(10,4) not null check(BMoney = 0), --账户余额 ) create table FixedAccounts( nID int primary key identity(1,1), --流水号 BNo varchar(20) not null references Depositors(BNo), --账号 BName varchar(20) not null, --姓名 BStyle varchar(20) not null check(BStyle = 定期存款 or BStyle = 定期取款), --操作类型 BMoney decimal(10,4) not null check(BMoney = 0), --存取金额 BYear int not null check(BYear = 1 or BYear = 2 or BYear = 3), --存款期限 BDate datetime not null --存款时间 插入触发器 create trigger InsertIntoCAorFA on Depositors after insert as declare @year int select @year = BYear from inserted if @year = 0 insert into CurrentAccounts(BNo,BName,BStyle,BDate,BMoney) select BNo,BName,BStyle,BDate,BMoney from inserted else insert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) select BNo,BName,BStyle,BMoney,BYear,BDate from inserted 删除触发器 create trigger DeleteFromCAorFA on Dep